Add A Number Of Months To Date

Oct 15, 2014

I have a simple query which returns a purchase date plus the number of months warranty an item has. What I need to do is add these months to the purchase date to get a warranty expiry date. What is the best way to do this? My query and results are below, I need an extra column in the results with that calculated warranty date:

select item, purchase_date, warranty_period
from inventory where item like '05MC%'

item purchase_date warranty_period
-------------------- ----------------------- ---------------
05MC0001 2014-07-22 00:00:00 36
05MC0002 2014-07-11 00:00:00 36

View 3 Replies


ADVERTISEMENT

Trying To Get A Series Of Number To Go By Months

Jul 12, 2007

if

(datalength

(cast_varchar

([Costpoint].[Profit_Calc_Consolidation].[PD])

) = 1)

then

(0 ||

cast_varchar

([Costpoint].[Profit_Calc_Consolidation].[PD]))

else

(cast_varchar

([Costpoint].[Profit_Calc_Consolidation].[PD]))





Goal:

To be able to tell the Period number ([PD]) to change from an integer to a var_char so that it can be concatenated with the Fiscal Year. I.e. Fiscal Year || Period number to produce the following results.

2006 01

2006 02

2006 03

Etc……





Status of Problem:

The issue is that I can only get it done to the point to where the Fiscal Year || Period number yields the following results:

20061

200610

200611

200612

20062

Etc….

This is as issue since I am trying to get all the information from the past to a certain year and period. Using a filter that says



Fiscal Year + Period Number <= ?Prompt_Fiscal_Year? + ?Prompt_Period_Number?

So if I enter Year: 2007 Period 3

I should get:

2006 01

2006 02

2006 03

Etc…….

2006 11

2006 12

2007 01

2007 02

2007 03



Can you please let me know if my thinking is off or if my SQL is wrong, Thank you for your assistance in this matter.

View 1 Replies View Related

Calculate Number Of Admits In Past 12 Months

Aug 5, 2013

I have a report that has 41 fields one of these fields is the Admit Date. I need to create a field that shows a count of Admits in the past 12 months .

View 6 Replies View Related

Transact SQL :: Number Of Distinct Users In Last 3 / 6 / 9 / 12 Months

Aug 26, 2015

I have table A(year int, month int, user varchar(20)), and I am trying to write a view that would show number of distinct users in the last 3 months, last 6 months, last 9 months and last 12 months(all from the most recent year/month) in following format:

3 months     6 months     9 months     12  months

number of distinct users        x                  y                  z                      w

View 20 Replies View Related

SQL Server 2012 :: Number Of Months Between Two Dates In YYYYMM Format

Jun 15, 2015

I am looking to calculate no of months between two dates which are in YYYYMM format.

Like no of months between 201505 and 201305

View 7 Replies View Related

SQL Server 2008 :: Calculate Number Of Months Between Dates For Multiple Records?

Oct 7, 2015

I have a challenge and I'm not sure the best route to go. Consider the following dataset.

I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.

CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012

What I want to know is the average number of months between the purchases. And do this for each customer.

View 6 Replies View Related

Power Pivot :: Revenue Schedules - Creating Rows By Number Of Months

May 26, 2015

I am trying to calculate how much revenue we may get, based on potential new business opportunities. The core fields we have are

Total Contract Value ($ or £)Duration of contract (months)Revenue start dateVarious information about the new business - ID, Title, Customer etc.

We can easily calculate the revenue per month with "Total Contract value divide by duration".

However what I would really like to do is be able to know how much revenue we will be getting each month.

To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But how to create the appropriate months and the correct amount of rows.

View 3 Replies View Related

How To Get Date From 3 Months Ago

Oct 10, 2006

Hi all,How to get the date from the 3 months ago? So, if user enter a date (10/10/2006), how can I get to 07/10/2006.Thanks in advance...

View 3 Replies View Related

Transact SQL :: Find Missing Months In A Table For The Earliest And Latest Start Dates Per ID Number?

Aug 27, 2015

I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:

create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'

For the above table, the answer should be:

ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01

1) don't include an ID column,

2) don't use the start date/end dates in the data or

3) use cursors, which are forbidden in my environment.

View 9 Replies View Related

SELECT Where Date &>= 14 Months Ago

Nov 29, 2004

Hi,

I need a select statement with the condition that the date (a timestamp with format yyyy-mm-dd) is either equal to or longer than 14 months from today's date.

I am using SQL Server

Can anyone point me in the right diretion?

Thanks

View 1 Replies View Related

Date Difference In Months

Feb 24, 2006

Hi to all,

I want to make an sql query that will subtract two dates and the difference will be number in months.

i have this table TestTable with Fields ACost, Adate, AMonth

my formula is this X = (Now-Adate), should give me number in months
and Y = (ACost/AMonth)
i want to get the product of X and Y, (X*Y).

how can i make this in sql views?

can anyone help me with this?

thanks a lot!


best regards,

saldiboy

View 3 Replies View Related

Expression Where Date Is 3 Months Ago?

Apr 17, 2008

What is the correct code for an expression where my "datetime" field is greater then or equal to 3 months ago?

View 1 Replies View Related

Date In Current Year Or Last Two Months

Apr 17, 2008

Hello

I had a requirement to filter records for the current year only... I went back to the user and explained that the logic wasn't sound... eg if somebody did the search early in January then they would probably get no results back. My suggestion was to go back two months if the current date is in January or february.

So, this is what I have.......




Code Snippet

where cs.startdate > case when year(cs.startdate) = year(getdate()) and month(getdate()) > 2 then
convert(char(4),year(getdate()))+'-01-01' else dateadd(month,-2,getdate()) end





Is there a neater/better way of coding this?

Jon

View 5 Replies View Related

SQL 2012 :: How To Calculate Date Difference In Months

Jan 23, 2015

I would like to calculate difference between end_date and current date in Months.And also how we can calculate the sum of difference in months between start_date and end_date for each ID?

CREATE TABLE datedifference (
id INT
,start_date INT
,end_date INT
)
INSERT INTO datedifference VALUES (10,20091202,20100629)
INSERT INTO datedifference VALUES (20,20071202,20090330)
INSERT INTO datedifference VALUES (30,20051202,20101031)

View 6 Replies View Related

T-SQL (SS2K8) :: Date Logic - How To Get Previous 6 Months Data

Sep 29, 2014

I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.

WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)

[Code] ...

Current output what i am getting:

Expected Output:

I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.

But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.

View 6 Replies View Related

How To Find Next 6 Months Dates Basing On Current Date

Jan 28, 2015

I need to find out All mondays / Tuesdays etc dates for next 6 months from the current date.

I have a table called DayOfWeek and the records are

1Sun
2Mon
3Tue
4Wed
5Thu
6Fri
7Sat

so if I pass "1" to the stored procedure, I need to find all Sunday dates for the next 6 months

And so if I pass "2" to the stored procedure, I need to find all Monday dates for the next 6 months...etc

View 5 Replies View Related

Transact SQL :: Last Record Not In Past 9 Months From Todays Date?

Aug 20, 2015

I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.

MC refers to Maintenance.

MCLog data:

carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15

[Code] .....

Here is my Table:

CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));

[code]....

View 6 Replies View Related

T-SQL Scritp To Calculate The Last 6 Months Of Data In A Date Field.

Oct 12, 2006

Hi there,

if any body out there has done this before, can you please post the T-SQL script that calculate whatever last months of data based on a Date field.

Thanks very much,



Manny

View 2 Replies View Related

T-SQL (SS2K8) :: Pass Dates For Previous Months Start And End Date

Mar 16, 2015

I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1

View 1 Replies View Related

Power Pivot :: Date Dimension - Create A List Of Months

Jul 21, 2015

I need to create a date dimension where the lowest level is month. I've seen examples which use the list function such as

Source = List.Dates(#date(2000, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2000,1,1)), #duration(1,0,0,0)),

The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g

1/1/2000
1/2/2000
1/3/2000
etc..

I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates.

View 2 Replies View Related

SQL Server 2008 :: Count How Many Records Within 6 Months From Current Record Date

May 27, 2015

My data has 2 fields: Customer Telephone Number, Date of Visit.

Basically I want to add a field ([# of Visits]), which tells me what number of visit the current record is within 6 months.

Customer TN | Date of Visit | # of Visits (Within 6 month - 180 days)
1111 | 01-Jan-2015 | 1
1111 | 06-Jan-2015 | 2
1111 | 30-Jan-2015 | 3
1111 | 05-Apr-2015 | 4
1111 | 07-Jul-2015 | 3

As you can see, the last visit would counts as 3rd because 180 days from 07-Jul-2015 would be Jan-8-2015.

View 3 Replies View Related

Reporting Services :: Displaying Previous Months On The Basis Of Start Date

Nov 4, 2015

I have a ssrs report which has a parameter start date.i have made 12 columns acc to client req which i cannot change:

jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec

These columns are dynamic acc to start date for which i have used this exp for first cell:

=monthname(month(Parameters!Startdate.Value),true) and for next 11 cells accordingly.

These 12 columns are grouped quaterly which i have made statically again for clien req which i cannot change so they are grouped staticaly by me in report like:

q12015      |q2  2015     |q32015      |q42015
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec  

exp for quarterly is :

="Q" & DatePart(DateInterval.Quarter, Parameters!Startdate.Value) &  year(Parameters!Startdate.Value).

Now when i select my start date of months jan,apr,july and oct report work fine.... but when i select my start date months of feb,may,aug and nov then reportis something like: (lets say i choose feb)

q12015        |q22015     |q32015      |q42015
feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec|jan.

What is happening is in q1 feb,mar and apr are coming and what i want in q1 is jan,feb and mar.

And in q4 nov ,dec and jan is coming and what is want in  q4 is oct,nov and dec.

So generally what is am asking is how to display jan in this condition in my first cell.same apllies for rest of months..

Similarly when i select my start date months of mar,june,sep,dec i want previous two months to be displayed.

View 5 Replies View Related

Selecting Data Within 1 Month, 6 Months, 12 Months

Mar 27, 2008



I have the following table



FeedBack Type Date

test2 positive 03/15/08

tes3 negative 03/01/08

.. ....



in my page i need to select the number of negative/positive comments within the last



1 month, 6 months, 12 months



How can I accomplish that?
thanks

View 5 Replies View Related

Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)

Apr 30, 2015

So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format

NUMERIC(8,0) CCYYMMDD?

I tried this...

SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;

but that put it in the format of...

AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''

Which is close...I think I just need to lose the "-"

View 5 Replies View Related

SQL Server 2012 :: Calculate Number Of Days Based On Computer System Date And Due Date Row

Mar 18, 2014

I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.

Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.

View 2 Replies View Related

Need To CalcuThe Number Of Days Between The Current Date And A Stored Date

Sep 22, 2006

I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.

View 4 Replies View Related

Long Number To Date

Jun 7, 2007

Hi,
I am trying to convert a date from long number to a date format. Can someone please help me.

Example:
Database shows the following:
TimeCreated
1181224346

I would like to see the following
TimeCreated
06-07-2007

Thanks,

View 4 Replies View Related

How Can I Get The Date From Week Number

Jan 17, 2008



I have week number and i want to get the date from that
for example Week=52 will be the first day of that day which is monday 12/24/2007


Thanks

View 9 Replies View Related

Getting The Date From Week Number

Oct 7, 2007

I have a column in my dataset that contains the week number.

I would like to use 'Expression' Or code function to do that
how can i get the date from week number? for example the week number is 38

so i wil get this output: 09/17/07 - 09/23/07 (the week starts on Monday and ends on Sunday)
Thanks

View 10 Replies View Related

Format Date And Number!

Jun 12, 2007

Dear Friens,

I'm having some problem to format my date and number fields!! :-(

In my column I have a date in the format 2007-05-28 00:00:00, and I want it to 28-05-2007.



And the field number I have in the format 28377497636.4987 and I need or 28.377.497.636,4987



or 28.377.497.636.4987.



Could you help me?

Thanks!!

View 10 Replies View Related

Number Of 'Periods' In A Date Range

Feb 10, 2006

Guys
Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number
of periods betwen the startdate and enddate
so for example

DECLARE @Periods TABLE
(
StartDate datetime,
Enddate datetime,
NumberOfMonthsInPeriod INT ,
TotPeriodsINT
)

-- Initial data
insert @Periods
(
StartDate ,
Enddate ,
NumberOfMonthsInPeriod ,
TotPeriods-- for this illustration, initialised to 0 but need to be UPDATEd as per detail below
)

select
'30-Sep-2005',
'10-Apr-2009',
1 -- 1 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
3 -- 3 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
6 -- 6 month period
0

The following rules regarding periods apply
Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie
01-Jan - 31 Jan
01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...)
and so on to December

Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year
01-Jan - 31-Mar
01-Apr - 30-Jun
01-Jul - 30-Sep
01-Oct - 31-Dec

Each 6 (NumberOfMonthsInPeriod) month period is one of the following
01-Jan - 30-Jun
01-Jul - 31-Dec

I need to derive TotPeriods as follows:

For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until
last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44

For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until
last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16

For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until
last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8

Hope this is clear and thanks in advance

View 3 Replies View Related

Converting Date To Number And Concatenating

Nov 1, 2013

I've converted a date field to a number. I'd then like to concatenate this with another number field.

(CONVERT(bigint,convert(datetime,[Date of Incident])) + ' ' + cast([Incident Number]as nvarchar(4))) as 'lookup'

Using the code above only returns the converted date to number field.

View 2 Replies View Related

Get Date From Week Number In A Year

Feb 11, 2014

Can I get a date from a week number in a certain year, like I know it's week 7 in 2014 (I may know the month.)

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved