Sql Server string to date conversion

Question

I want to convert a string like this:

'10/15/2008 10:06:32 PM'

into the equivalent DATETIME value in Sql Server.

In Oracle, I would say this:

TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM')

This question implies that I must parse the string into one of the standard formats, and then convert using one of those codes. That seems ludicrous for such a mundane operation. Is there an easier way?

Answer 1

In SQL Server Denali, you will be able to do something that approaches what you're looking for. But you still can't just pass any arbitrarily defined wacky date string and expect SQL Server to accommodate. Here is one example using something you posted in your own answer. The FORMAT() function and can also accept locales as an optional argument - it is based on .Net's format, so most if not all of the token formats you'd expect to see will be there.

DECLARE @d DATETIME = '2008-10-13 18:45:19';

-- returns Oct-13/2008 18:45:19:
SELECT FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss');

-- returns NULL if the conversion fails:
SELECT TRY_PARSE(FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss') AS DATETIME);

-- returns an error if the conversion fails:
SELECT PARSE(FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss') AS DATETIME);

I strongly encourage you to take more control and sanitize your date inputs. The days of letting people type dates using whatever format they want into a freetext form field should be way behind us by now. If someone enters 8/9/2011 is that August 9th or September 8th? If you make them pick a date on a calendar control, then the app can control the format. No matter how much you try to predict your users' behavior, they'll always figure out a dumber way to enter a date that you didn't plan for.

Until Denali, though, I think that @Ovidiu has the best advice so far... this can be made fairly trivial by implementing your own CLR function. Then you can write a case/switch for as many wacky non-standard formats as you want.

---

UPDATE for @dhergert:

SELECT TRY_PARSE('10/15/2008 10:06:32 PM' AS DATETIME USING 'en-us');
SELECT TRY_PARSE('15/10/2008 10:06:32 PM' AS DATETIME USING 'en-gb');

Results:

2008-10-15 22:06:32.000
2008-10-15 22:06:32.000

You still need to have that other crucial piece of information first. You can't use native T-SQL to determine whether 6/9/2012 is June 9th or September 6th.

Answer 2

why not try

select convert(date,'10/15/2011 00:00:00',104) as [MM/dd/YYYY]

date formats can be found at SQL Server Helper > SQL Server Date Formats

Answer 3

For this problem the best solution I use is to have a CLR function in Sql Server 2005 that uses one of DateTime.Parse or ParseExact function to return the DateTime value with a specified format.

Answer 4

Personally if your dealing with arbitrary or totally off the wall formats, provided you know what they are ahead of time or are going to be then simply use regexp to pull the sections of the date you want and form a valid date/datetime component.