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

Sql Server 2008: Conversion From Text data type to Datetime Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 4:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 3:48 AM
Points: 62, Visits: 212
Hi All,

How do I convert a text data type column to datetime?
The query below returns this error: Explicit conversion from data type text to datetime is not allowed.

SELECT CONVERT(DATETIME, vchValue) FROM dtlConfiguration WHERE vchParameter = 'LastRTADImport'

The data in 'vchValue' column is in this format: 'Jul 21 2010 10:15AM'

Regards
Teee
Post #1395028
Posted Tuesday, December 11, 2012 4:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 AM
Points: 5,219, Visits: 5,074
Convert to VARCHAR then convert to datetime.

CONVERT(DATETIME,CONVERT(VARCHAR,vchValue))

The following link details what can and cannot be converted

http://msdn.microsoft.com/en-us/library/ms187928.aspx




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1395032
Posted Tuesday, December 11, 2012 4:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 13, 2013 4:35 AM
Points: 386, Visits: 211
Are you sure that is causing the problem?
This should work

select convert(datetime, 'Jul 21 2010 10:15AM')

Could be due to your settings maybe.



Cursors never.
DTS - only when needed and never to control.
Post #1395033
Posted Tuesday, December 11, 2012 4:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 AM
Points: 5,219, Visits: 5,074
nigelrivett (12/11/2012)
Are you sure that is causing the problem?
This should work

select convert(datetime, 'Jul 21 2010 10:15AM')

Could be due to your settings maybe.


The question and topic title say they are converting from TEXT to DATETIME, you cannot convert TEXT directly to DATETIME, you have to do an implicit conversion to VARCHAR then to DATETIME




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1395035
Posted Tuesday, December 11, 2012 4:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 3:48 AM
Points: 62, Visits: 212
Thanks for the link and the example posted above works perfectly.
Post #1395040
Posted Tuesday, December 11, 2012 4:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 AM
Points: 5,219, Visits: 5,074
Teee (12/11/2012)
Thanks for the link and the example posted above works perfectly.


Happy to help




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1395041
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse