x

DATE DIFF for YYYY-MM format

Hello,

How to get the different days using the DATEDIFF(DAY, YYYY-MM, YYYY-MM) formula.

 SELECT DATEDIFF(DAY, '2017-01-01', '2017-01-31') -- Returns 30 
 SELECT DATEDIFF(DAY, '2017-01', '2017-01')       -- Returns 30 -- I need this format

OR

 SELECT DATEDIFF(DAY, '2017-02-01', '2017-02-28') -- Returns 27
 SELECT DATEDIFF(DAY, '2017-02', '2017-02')       -- Returns 27 -- I need this format

OR

 SELECT DATEDIFF(DAY, '2017-01-01', '2017-02-28') -- Returns 58
 SELECT DATEDIFF(DAY, '2017-01', '2017-02')       -- Returns 58 -- I need this format

Thanks for your help.

more ▼

asked Feb 15 at 04:54 PM in Default

avatar image

Allenb
210 3 8

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

how You want to use '2017-02' ,this is not a date? so you cannot use datediff, sql server return error that he can't convert string to datetime , if you have a column with date, in one column you can calculate datediff and in the next convert date to string a extract year and month

more ▼

answered Feb 15 at 07:41 PM

avatar image

Lukasz8519
76 2

(comments are locked)
10|1200 characters needed characters left

Given that you are on SQL Server 2012, you could use the eomonth() function to work out the end date of the month

 declare @startdate varchar(10)
 declare @enddate varchar(10)
 
 set @startdate = '2017-01' --meaning the first of the month for Jan 2017
 set @enddate = '2017-01' --meaning the end of the month for Jan 2017
 
 select
     datediff (day,
             cast(@startdate + '-01' as date),
             eomonth(cast(@enddate + '-01' as date))
             )
 
 set @startdate = '2017-02' --meaning the first of the month for Feb 2017
 set @enddate = '2017-02' --meaning the end of the month for Feb 2017
 
 select
     datediff (day,
             cast(@startdate + '-01' as date),
             eomonth(cast(@enddate + '-01' as date))
             )
 
 set @startdate = '2017-01' --meaning the first of the month for Jan 2017
 set @enddate = '2017-02' --meaning the end of the month for Feb 2017
 
 select
     datediff (day,
             cast(@startdate + '-01' as date),
             eomonth(cast(@enddate + '-01' as date))
             )

For those of you without SQL Server 2012....

 select
     datediff (day,
             cast(@startdate + '-01' as date),
             dateadd(month, datediff(month, 0, cast(@enddate + '-01' as date)) + 1, -1)
             )    

more ▼

answered Feb 15 at 08:14 PM

avatar image

Kev Riley ♦♦
65.2k 48 62 81

(comments are locked)
10|1200 characters needed characters left

You have to create a script for this here you may try this code:

 create function fnagecalc( @date datetime)
 returns varchar(250)
 as begin
 
 declare  @years int, @months int, @days int , @tempdate datetime, @age varchar(50)
  begin
  set @tempdate=@date
 
 set @years= datediff(year,@tempdate,getdate())- case when (month(@date)>month(getdate())) 
             or (month(@date)=month(getdate()) and day(@date)>day(getdate())) then 1 else 0 end
 
 set @tempdate=dateadd(year,@years,@tempdate)
 
 
 set @months= datediff(month,@tempdate,getdate())- case when (day(@date)>day(getdate())) then 1 else 0 end
 
 set @tempdate=dateadd(month,@months,@tempdate)
 
 set @days=datediff(day,@tempdate,getdate())
 
 
 set @age= cast(@years as varchar(50))+ ' Years '+' '+cast(@months as varchar(50))+ ' Months ' +' '+cast(@days as varchar(50))+ ' Days '
 end
 return @age
 end 

You may check O/p. select dbo.fnagecalc('1999-08-11')

more ▼

answered Feb 16 at 04:54 AM

avatar image

jason_clark03
425 3 7

this doesn't quite answer the OPs question

Feb 16 at 08:22 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x368

asked: Feb 15 at 04:54 PM

Seen: 81 times

Last Updated: Feb 16 at 08:22 AM

Copyright 2016 Redgate Software. Privacy Policy