I have date data stored in a string field like this: "01/01/2000 4:56:00pm"
I need to compare that to the current date on the server. Is there a CDate type function I can use?
asked Oct 23, 2009 at 12:53 PM in Default
Steve Jones - Editor ♦♦
Surely '01/01/2000 4:56:00pm' is pretty safe since it is always the first of January whether you read it as dd/mm/yyyy or mm/dd/yyyy so cast will work fine. (perhaps it will throw an error in Japan)
This is a trick question of course.. If you aren't convinced, then try this
and you get...
See the difference. Yes, try using CAST and you are on a wing and a prayer. You are at the mercy of your database settings. For us in Britain this can be a nightmare as you may not be certain whether the date in the data you are importing comes from over the water or from our European colleagues. (which is why we like using ISO 8601 dates for transferring information.
My recommendation is to understand which date format it is, use CONVERT and the Date and Time Styles and avoid using CAST for importing data from sources with a different date format.
So to compare the date to now, use....
but with steve's original example you can do either because the date comes out the same.. until someone changes the date!
answered Oct 23, 2009 at 02:31 PM
First, you can use ISDATE to validate that the string is a valid date time data type. To convert it to a date you can use CAST like this:
CAST ('01/01/2000 4:56:00' TO DATETIME)
answered Oct 23, 2009 at 01:46 PM
Grant Fritchey ♦♦
I tested this to be sure because the answer seemed too easy, but you can just cast the string to datetime2 like this:'
To avoid errors, however, it might be better to use this:
EDIT: Original question asked about comparison to GETDATE().