May 28, 2012 at 8:44 am
Why do you need to change from DATE to DATETIME?
May 28, 2012 at 8:48 am
It give me this error:
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
May 28, 2012 at 8:51 am
Sachin 80451 (5/28/2012)
It give me this error:Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.
DOH, my bad, forgot I was in the 2005 Forum, DATE is a 2008 datetype only.
Yeah as your geting a DATETIME value back you will have a timestamp, so you will need to do a but more to get it to calculate midnight.
I will knock something up.
May 28, 2012 at 8:57 am
--To get 23:59:59 of the current day
SELECT CONVERT(DATETIME,LEFT(CONVERT(NVARCHAR,GETDATE(),120),10)+' 23:59:59.997')
--To get 00:00:00 of the next day
SELECT CONVERT(DATETIME,LEFT(CONVERT(NVARCHAR,GETDATE()+1,120),10)+' 00:00:00.000')
--To get 23:59:59 of the next day
SELECT CONVERT(DATETIME,LEFT(CONVERT(NVARCHAR,GETDATE()+1,120),10)+' 23:59:59.997')
That will do what you need, and might make it easier to understand, but you will need to do DueDate <= A 23:59:59.997 value incase you have something which is set to be DueOn 2012-05-28 23:59:59.997
Could change it to 23:59:59.999 in the CONVERT function and it will rollover to the next day due to the precision of DATETIME values so you could keep it as just a < but its up to you how you want your code to look
May 28, 2012 at 9:21 am
Hi Anthony these work. But where would I add the part to get records from the current time until before midnight?
May 28, 2012 at 10:17 am
Just use due date > getdate() and due date <= the first expression in my last post
May 28, 2012 at 12:06 pm
anthony.green (5/28/2012)
--To get 23:59:59 of the current daySELECT CONVERT(DATETIME,LEFT(CONVERT(NVARCHAR,GETDATE(),120),10)+' 23:59:59.997')
--To get 00:00:00 of the next day
SELECT CONVERT(DATETIME,LEFT(CONVERT(NVARCHAR,GETDATE()+1,120),10)+' 00:00:00.000')
--To get 23:59:59 of the next day
SELECT CONVERT(DATETIME,LEFT(CONVERT(NVARCHAR,GETDATE()+1,120),10)+' 23:59:59.997')
That will do what you need, and might make it easier to understand, but you will need to do DueDate <= A 23:59:59.997 value incase you have something which is set to be DueOn 2012-05-28 23:59:59.997
Could change it to 23:59:59.999 in the CONVERT function and it will rollover to the next day due to the precision of DATETIME values so you could keep it as just a < but its up to you how you want your code to look
I would use the conversion above. I also would be sure to use an open-ended range on the upper end of a datetime range (i.e. < '2012-05-29 00:00:00.000', instead of <= '2012-05-28 23:59:59.997').
Instead of this:
SELECT CONVERT(DATETIME,LEFT(CONVERT(NVARCHAR,GETDATE()+1,120),10)+' 00:00:00.000')
I'd use this:
select dateadd(dd, datediff(dd, 0, getdate()) + 1, 0)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply