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

Convert Varchar to datetime Expand / Collapse
Author
Message
Posted Wednesday, April 29, 2009 8:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 5:09 PM
Points: 94, Visits: 376
I have a data source that provides dates in a varchar format (e.g. AUG 28, 2008@11:30:03) that I need to convert to a datetime format. Any suggestions?

Thanks in advance.
Post #706874
Posted Wednesday, April 29, 2009 8:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,220, Visits: 2,614
How about this...

select Cast(Replace('AUG 28, 2008@11:30:03', '@', ' ') as datetime)



Ken Simmons
http://twitter.com/KenSimmons
Post #706897
Posted Wednesday, April 29, 2009 9:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Just a slight addition to Ken's (correct!) answer:
SELECT CONVERT(DATETIME, REPLACE('AUG 28, 2008@11:30:03', '@', ' '), 109)

CONVERT with style 109 ensures that the correct format is taken.

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #706921
Posted Thursday, April 30, 2009 7:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 5:09 PM
Points: 94, Visits: 376
Just what the doctor ordered. Thanks a bunch.
Post #707675
Posted Thursday, February 11, 2010 11:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 18, 2011 10:42 PM
Points: 123, Visits: 121
CREATE TABLE [dbo].[_CalendarDate](
[F1] [varchar](20) NULL
) ON [PRIMARY]

select * from dbo._CalendarDate

8242004
8252004
8262004

select CONVERT(datetime, F1, 103) from _CalendarDate

Syntax error converting datetime from character string.

I want to convert it to datetime, any idea?



Post #864209
Posted Thursday, February 11, 2010 11:38 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 20,705, Visits: 32,356
CooLDBA (2/11/2010)
CREATE TABLE [dbo].[_CalendarDate](
[F1] [varchar](20) NULL
) ON [PRIMARY]

select * from dbo._CalendarDate

8242004
8252004
8262004

select CONVERT(datetime, F1, 103) from _CalendarDate

Syntax error converting datetime from character string.

I want to convert it to datetime, any idea?


Hard to say. Looks like the dates are in mmddyyyy format, but potentially with no leading zeros. If there are no leading zeros in the DD portion, it could be problematic. From your sample data, I have no clue.



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 #864214
Posted Thursday, February 11, 2010 2:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 18, 2011 10:42 PM
Points: 123, Visits: 121
I was able to change the data to
08-24-2004
08-25-2004
08-26-2004

select CONVERT(datetime, F3, 103) from _CalendarDate

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(1 row(s) affected)

any clue?



Post #864349
Posted Thursday, February 11, 2010 3:10 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 20,705, Visits: 32,356
CooLDBA (2/11/2010)
I was able to change the data to
08-24-2004
08-25-2004
08-26-2004

select CONVERT(datetime, F3, 103) from _CalendarDate

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(1 row(s) affected)

any clue?


try this:

select CONVERT(datetime, F3, 110) from _CalendarDate




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 #864358
Posted Thursday, February 11, 2010 3:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 18, 2011 10:42 PM
Points: 123, Visits: 121
Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

It didn't make sense as if I do:

SELECT convert(datetime,'2004-08-24',120)

2004-08-24 00:00:00.000

It works!



Post #864379
Posted Thursday, February 11, 2010 4:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 18, 2011 10:42 PM
Points: 123, Visits: 121
Sorry guys. I figured it out.

There is erroneous data value in the table. =(



Post #864387
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse