April 15, 2009 at 2:50 am
Hi,
I'm trying to prepare a SELECT statement to retrieve entries in a table made before a date, which could be any date or time.
If my statement reads
SELECT COLUMN_NAME FROM TABLE WHERE LAST_UPDATED_DATE < GETDATE()
then I get all entries, which is no real surprise. If I try
SELECT TALLY_NAME FROM TALLYS WHERE LAST_UPDATED_DATE < '15/04/2009'
then I get the error message
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
If I try
SELECT TALLY_NAME FROM TALLYS WHERE LAST_UPDATED_DATE < 15/04/2009
then I get no returned data, but no error messages.
How can I pass a specific DateTime parameter to retrieve my entries?
Thanks
Tony
April 15, 2009 at 2:55 am
SELECT TALLY_NAME FROM TALLYS WHERE LAST_UPDATED_DATE < '04/15/2009'
try this...
April 15, 2009 at 3:07 am
I normally always use this format 'YYYY-MM-DD'
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 15, 2009 at 3:07 am
Many thanks for that.
Silly me for not realising that I had to use American Date Format when all the entries are in a UK format.....
Adding to that, would it be possible to add a time in to that scenario then, eg.
'MM/DD/YYYY HH:MM:SS'?
April 16, 2009 at 5:09 am
Yes, you can definitely add time to your string in that format.
April 16, 2009 at 6:11 am
tony (4/15/2009)
Many thanks for that.Silly me for not realising that I had to use American Date Format when all the entries are in a UK format.....
Adding to that, would it be possible to add a time in to that scenario then, eg.
'MM/DD/YYYY HH:MM:SS'?
You don't have to use the US date format here - which I personally think is a stupid convention. I try to use one of the following formats when specifying dates in a TSQL script, which should work for all locales and configurations of SQL Server.
date only:
'yyyyMMdd' (Convert style 112 - ISO)
date and time:
'yyyyMMdd HH:mm:ss' (Convert style 112 + 108)
or
'yyyy-MM-ddTHH:mm:ss' (Convert style 126 without milliseconds - ISO8601)
or
'yyyy-MM-ddTHH:mm:ss.fff' (Convert style 126 with milliseconds - ISO8601)
April 16, 2009 at 6:16 am
How about using this option, to preserve your datetime format:
SELECT TALLY_NAME FROM TALLYS WHERE LAST_UPDATED_DATE < CONVERT(DATETIME,'15/04/2009',103)
--103 STANDARD
April 16, 2009 at 7:42 am
Christopher Stobbs (4/15/2009)
I normally always use this format 'YYYY-MM-DD'
Unfortunately, the 'YYYY-MM-DD' format is not interpreted 100% consistently either though the ISO8601 datetime format 'YYYY-MM-DDTHH:MM:SS' is. For example, you can set the DATEFORMAT to ydm in SQL Sever, though I've no idea why anyone would want to do this unless they were deliberately trying to cause confusion. In the following, the strings '2009-01-12' and '20090112' are interpreted as different dates, and '2009-01-12 14:30:10' is interpreted as a different date from '2009-01-12T14:30:10'.
SET DATEFORMAT ydm
GO
SELECT CONVERT(datetime, '2009-01-12'), CONVERT(datetime, '20090112')
SELECT CONVERT(datetime, '2009-01-12 14:30:10'), CONVERT(datetime, '2009-01-12T14:30:10')
I usually don't want to worry about different locales or configuration or SET DATEFORMAT, so I try to use a date/time format that is interpreted consistently regardless of everything.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply