x

Comparing dates and strings

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?

more ▼

asked Oct 23, 2009 at 12:53 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

Select convert(datetime, '02/01/2000 4:56:00pm',101)
--us date-time convention

Select convert(datetime, '02/01/2000 4:56:00pm', 103)
--british/French date-time convention

Select convert(datetime, '01/01/02  4:56:00pm', 11)
--Japan

and you get...

-----------------------
2000-02-01 16:56:00.000

(1 row(s) affected)


-----------------------
2000-01-02 16:56:00.000

(1 row(s) affected)


-----------------------
2001-01-02 16:56:00.000

(1 row(s) affected)

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....

      Select DateDiff(Month,
                      convert(datetime, '02/01/2000 4:56:00pm',101),
                      GetDate())

but with steve's original example you can do either because the date comes out the same.. until someone changes the date!

more ▼

answered Oct 23, 2009 at 02:31 PM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

Very good points about CAST vs CONVERT. CAST worked for me (in the US) for several test dates, but I agree CONVERT is better.
Oct 23, 2009 at 02:46 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left

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)

more ▼

answered Oct 23, 2009 at 01:46 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.9k 19 21 74

(comments are locked)
10|1200 characters needed characters left

I tested this to be sure because the answer seemed too easy, but you can just cast the string to datetime2 like this:'

CAST(date_string_field AS datetime2)

To avoid errors, however, it might be better to use this:

SELECT
    CASE
    	WHEN ISDATE(date_string_field)=1 THEN CAST(date_string_field AS datetime2)
    	ELSE null
    END

EDIT: Original question asked about comparison to GETDATE().

DATEDIFF(  interval,  CASE  WHEN ISDATE(date_string_field)=1  THEN CAST(date_string_field AS datetime2)  ELSE null  END,  GETDATE() ) 
more ▼

answered Oct 23, 2009 at 02:16 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x43

asked: Oct 23, 2009 at 12:53 PM

Seen: 2618 times

Last Updated: Oct 23, 2009 at 12:53 PM