March 3, 2009 at 12:25 am
hi all,
can anybody help me in writing the query for last date of a year
thanks in advance
regards
durgesh J
March 3, 2009 at 12:34 am
How about this
/* If you are starting from a datetime */
DECLARE @date datetime
SELECT @date = '2009-01-01'
SELECT DATEADD(year, DATEDIFF(year, -1, @date), -1)
/* or */
SELECT DATEADD(year, DATEPART(year, @date) - 1899, -1)
/* or */
SELECT CONVERT(datetime, CONVERT(varchar(4), DATEPART(year, @date)) + '1231')
/* If you are starting from the year stored as an int */
DECLARE @year int
SELECT @year = 2009
SELECT DATEADD(year, @year - 1899, -1)
/* or */
SELECT CONVERT(datetime, CONVERT(varchar(4), @year) + '1231')
March 3, 2009 at 1:29 am
SELECT date,DateAdd(yy,Datediff(yy,-1,getdate()),-1)
FROM TableName
John Smith
March 4, 2009 at 4:29 pm
Mangal,
You've given an interesting solution that apparently depends in several ways on internals of MSSQL. Your -1 operand in the datediff could have been the slightly less obscure '1899'. In either case, this construction depends on datetime values being internally represented in part as a count of days since 30 December 1899. While it works, I'd be hesitant to use this in a production situation, not being sure that it would hold under SQL 2008 or later versions and certainly would not want to puzzle the next person to have to maintain this code.
Could you explain exactly how it works and perhaps address my concerns about using this approach?
March 4, 2009 at 4:56 pm
john.arnott (3/4/2009)
Mangal,You've given an interesting solution that apparently depends in several ways on internals of MSSQL. Your -1 operand in the datediff could have been the slightly less obscure '1899'. In either case, this construction depends on datetime values being internally represented in part as a count of days since 30 December 1899. While it works, I'd be hesitant to use this in a production situation, not being sure that it would hold under SQL 2008 or later versions and certainly would not want to puzzle the next person to have to maintain this code.
Could you explain exactly how it works and perhaps address my concerns about using this approach?
This following two queries do exactly the same thing.
select dateadd(yy,datediff(yy,-1,getdate()),-1)
select dateadd(yy,datediff(yy,'18991231',getdate()),'18991231')
The "zero" point for SQL Server datetime values is 1900-01-01, so if you cast the integer 0 to a datetime, it produces the datetime value 1900-01-01 00:00:00.000. A value of -1 will be cast to 1899-12-31, since it is one day before 1900-01-01, and -2 will be cast to 1899-12-30, and so on.
This default casting of integers to datetime is well documented, so I would not call it depending on knowledge of SQL Server internals. If you feel more comfortable, you can code it the way I showed in the second statement.
The same method can also be used for end of quarter, and end of month:
select
EndOfYear= dateadd(yy,datediff(yy,-1,getdate()),-1),
EndOfQuarter= dateadd(qq,datediff(qq,-1,getdate()),-1),
EndOfMonth= dateadd(mm,datediff(mm,-1,getdate()),-1)
March 4, 2009 at 5:13 pm
Michael,
Thank you. That really helps me understand. I had already tried substituting '1899' for -1 and found that it worked for the year evaluations, but now see that dateadd or datediff with a 'yy' parameter will simply count year boundaries between the two dates, and so return the same count (110 or whatever) regardless of the month and date portions. So, my '1899' was interpreted as 1899-01-01 and then the number of years till 2009 was still determined as 110. I had puzzled over why just the year value '1899' worked and now see that it would not had I tried to use the technique for months or quarters.
So, perhaps I will put this technique in my OK-to-Use drawer, but would use the '1899-12-31' form and certainly would include comments explaining it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply