April 13, 2018 at 3:50 am
Hi all,
Could somone explain why the following works for days other than 13/04?
DECLARE @stop_time DATETIME;
SET @stop_time = CONVERT(varchar(10),GETDATE()-1,103) + ' 23:59:59'
SELECT @stop_time
SET @stop_time = CONVERT(varchar(10),GETDATE()-2,103) + ' 23:59:59'
SELECT @stop_time
SET @stop_time = CONVERT(varchar(10),GETDATE()-3,103) + ' 23:59:59'
SELECT @stop_time
SET @stop_time = CONVERT(varchar(10),GETDATE()-4,103) + ' 23:59:59'
SELECT @stop_time
SET @stop_time = CONVERT(varchar(10),GETDATE(),103) + ' 23:59:59'
SELECT @stop_time
The final conversion errors and I don't see why 13/04 is any different to 12/04,11/04, 10/04 or 9/04 in terms of the conversion. (P.S. I know changing the style code to 120 prevents this but am trying to understand why this errors for now).
Any insight greatly appreciated.
Thanks . . . . Jason
April 13, 2018 at 3:57 am
PLease ignore question - i had missed the obvious and have figured it out.
April 13, 2018 at 3:59 am
Jason
13/04 is different from 12/04 and so on in that it can only be interpreted as dd/mm. The others were interpreted in your code as mm/dd, perhaps due to your local settings or the 103 style you specified in your CONVERT. My advice would be to use proper date arithmetic: add one day to the date, chop off the time portion and subtract one second.
John
April 13, 2018 at 4:07 am
Thanks John. I realised about 2 minutes after posting. It's code I have come across due to the error today and isn't how I'd have done it. My approach would have been pretty much along the lines you have suggested.
Cheers . . . Jason
April 13, 2018 at 8:10 am
Btw, just based on the pattern of code in your OP, it looks like you're doing a BETWEEN range on a datetime.
Put simply, DON'T do that, ever.
Instead, use < the next day. For example, for the current month, rather than:
--wrong method
WHERE datetime_column >= '20180401' AND datetime_column <= '20180430 23:59:59'
--correct method
WHERE datetime_column >= '20180401' AND datetime_column < '20180501'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 13, 2018 at 8:56 am
I would seriously recommend that you always use ISO formats when handling dates. That means YYYYMMDD or YYYY-MM-DDThh:mi:ss.msss
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply