Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

how to insert datetime column into table Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 7:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 7,224, Visits: 13,696
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;






“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1435910
Posted Wednesday, March 27, 2013 8:26 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 23,290, Visits: 32,017
Edit: Nevermind, should have kept reading before posting.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1435937
Posted Wednesday, March 27, 2013 10:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 51, Visits: 293
Tis why I avoid passing comment straight away
Post #1436027
Posted Wednesday, March 27, 2013 10:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 51, Visits: 293
Chris,

Often the style gets ignored or overridden by the character string being in a different state. It also keeps the time element which seems to me slighty at odds compared to convert this datetime to a date string.

I will try and encourage develoeprs to use a datetime type rather than passing a string and hoping that the database will alter it to something correct. XML has a proper datetime type as well.

And yes I have had my fingers burnt by the odd load coming in with some dates being in one format and another being in a different format, in the same file.
Post #1436036
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse