Steve JP (3/27/2013)
Steve JP (3/26/2013)
....
The issue with the convert is that the 3rd arguement is the style and is used to set the style when you convert from a datetime to a varchar, not the other way around.
...
ChrisM@Work (3/27/2013)
....
The style argument for converting a date as string in the format 'Mar 26 2013 12:49PM' to a datetime data type using CONVERT() is 100. Other style arguments are likely to fail. I'm sorry but I still don't see your point. None of this appears to prove anything either way. Have a quick read of CAST and CONVERT in BOL, specifically CONVERT relating to dates. The explanation is quite good. If you're still unsure, post back......
Chris, I thought my example was clear in that the style code ( of 25510 ) that I first used doesnt exist. I know this & you know this, but others may not, as this is the newbies section hence I posted the code. The next code block shows that the Style arguement is often ignored for a conversion from varchar to datetime. It also doesnt have any affect on the time. ie the 103 (which is English) will take a datetime to a varchar as only a date of format dd/mm/yyyy. But from a varchar to datetime will take the time element if it exists as well so can be a bit misleading for some. How many times have people asked how to truncate the time element from a datetime value.
If the style arguement doest exist but cant be applied then it will error. Other times like I have shown it will ignore the style arguement if the actual data contradicts the style.
One of the most reliable methods I have found to enter datetime is yyyy-mm-dd hh:mm:ss:mmm (or the full iso style)
Another option is to use SET DATEFORMAT, although I'm not certain what lifespan this has long term.
I havent got all day for these forums and can only post while jobs are running etc. So yes some of the time my comments are a bit too brief, but mainly I try and post a bit more than "go read the BOLs".
Hi Steve
I guess I owe you an apology for the brevity of my last reply. Here's something I knocked up earlier this morning which should help - seeing something in action makes the concept far more likely to "stick" than reading about it.
Match a date string to a style argument for conversion to a DATETIME, and show the output when the reverse is performed, DATETIME to VARCHAR:
;WITH RawData ([Standard], DateString, Style) AS (
SELECT 'DEFAULT', 'Mar 27 2013 11:57AM',100 UNION ALL
SELECT 'U.S.', '03/27/2013',101 UNION ALL
SELECT 'ANSI', '2013.03.27',102 UNION ALL
SELECT 'British/French', '27/03/2013', 103 UNION ALL
SELECT 'German', '27.03.2013',104 UNION ALL
SELECT 'Italian', '27-03-2013',105 UNION ALL
SELECT '-', '27 Mar 2013',106 UNION ALL
SELECT '-', 'Mar 27, 2013',107 UNION ALL
SELECT 'Default + milliseconds', 'Mar 27 2013 11:57:23:607AM',109 UNION ALL
SELECT 'USA', '03-27-2013',110 UNION ALL
SELECT 'JAPAN', '2013/03/27',111 UNION ALL
SELECT 'ISO', '20130327',112 UNION ALL
SELECT 'Europe default + milliseconds', '27 Mar 2013 11:57:23:607',113
)
SELECT r.[Standard], x.*
FROM RawData r
CROSS APPLY (
SELECT
[String To DATETIME] = CONVERT(DATETIME,r.DateString,r.Style),
[DATETIME to String] = CONVERT(VARCHAR(26), GETDATE(),r.Style)
) x;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden