Converting varchar to datetime

Question

Post reply

Converting varchar to datetime

ishaan99

SSCertifiable

Points: 6261
More actions
March 14, 2012 at 11:19 am

#252453

I have a varchar column with data as 10/28/2011 11:47:55.686455 AM
whats the best way to convert it to datetime datatype and store it in a datetime column. Any help wilbe greatly appreciated.
TIA

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

This?

DECLARE @DatetimeasVC VARCHAR(30) = '10/28/2011 11:47:55.686455 AM'

SELECT CAST ( @DatetimeasVC AS DATETIME2) UsingCAST ,

CONVERT(DATETIME2, @DatetimeasVC) UsingConvert

You yourself said u want to convert, why dint u try the CONVERT/CAST function???

ishaan99 SSCertifiable Points: 6261 More actions · Answer 2

I tried both doing a cast and convert still getting an error as

Conversion failed when converting datetime from character string. This is what i tried:

SELECT convert(varchar,convert(datetime,'1/6/2012 12:36:35.631951 PM'),100)

calvo SSChampion Points: 12930 More actions · Answer 3

The string is too long to be converted to a datetime data type.

try SELECT convert(datetime,'1/6/2012 12:36:35.631 PM')

Or use datetime2 as coldcoffee suggested in his example.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

ishaan99 (3/14/2012)
I tried both doing a cast and convert still getting an error as
Conversion failed when converting datetime from character string. This is what i tried:
SELECT convert(varchar,convert(datetime,'1/6/2012 12:36:35.631951 PM'),100)

That is becase you are trying to convert a varchar valus that is of the type DATETIME2 to DATETIME

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

A little busy right now, but it looks like you are going to have to do some reformating of your dates to convert. The format code 100 expects the date to be in the format mon dd yyyy hh:miAM (or PM) which is NOT the format of your dates.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

in the link i posted for DATETIME and DATETIME2, look at the Element Range explanation. That will tel u why ur string is not getting converted. Basically u had more than 3 digists for the fractional second element

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

calvo (3/14/2012)
The string is too long to be converted to a datetime data type.
try SELECT convert(datetime,'1/6/2012 12:36:35.631 PM')
Or use datetime2 as coldcoffee suggested in his example.

Based on the forum this is posted in, DATETIME2 isn't an option. This is in a SQL Server 2005 forum.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Like stated above you will have to do some string manipulation to remove the last 3 decimal places.

Aside from that the code you posted conflicts with what you are trying to do.

SELECT convert(varchar,convert(datetime,'1/6/2012 12:36:35.631951 PM'),100)

Why convert to datetime which is what you stated you want to store this as and then convert it back to a varchar??? Convert it to datetime and don't do the second convert.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ron McCullough SSC Guru Points: 63877 More actions · Answer 9

Now this is going to be slow and NOT the best solution, as others have stated the lenght of the data is too long. This might work for you.

DECLARE @T VARCHAR(30)

DECLARE @X VARCHAR(24)

SET @T ='1/6/2012 12:36:35.631951 PM'

SET @X = (SELECT SUBSTRING(@T,1,DATALENGTH(@T)-6))

SELECT CONVERT(DATETIME,@X,20)

SELECT CONVERT(DATETIME,@X,21)

Note that the conversion is NOT to a 24 hour clock time so you loose the ability to determine if it is AM or PM.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 10

bitbucket-25253 (3/14/2012)
Now this is going to be slow and NOT the best solution, as others have stated the lenght of the data is too long. This might work for you.
DECLARE @T VARCHAR(30)
DECLARE @X VARCHAR(24)
SET @T ='1/6/2012 12:36:35.631951 PM'
SET @X = (SELECT SUBSTRING(@T,1,DATALENGTH(@T)-6))
SELECT CONVERT(DATETIME,@X,20)
SELECT CONVERT(DATETIME,@X,21)
Note that the conversion is NOT to a 24 hour clock time so you loose the ability to determine if it is AM or PM.

If you need to use a 24 hour clock then this might do it for you

DECLARE @T VARCHAR(30)

DECLARE @X VARCHAR(24)

DECLARE @P VARCHAR(1)

SET @T ='1/6/2012 01:36:35.631951 AM'

SET @P =(SELECT SUBSTRING(@T,CHARINDEX('M',@T,1)-1,2))

SELECT @P AS 'PM OR am'

SET @X = (SELECT SUBSTRING(@T,1,DATALENGTH(@T)-6))

IF @P = 'P'

BEGIN

SELECT DATEADD (hh,12,CONVERT(DATETIME,@X,20)) AS 'PM'

END

ELSE

SELECT CONVERT(DATETIME,@X,20)

Again this will NOT be very fast for a large number of rows, might be best to do the conversion from VARCHAR to DATETIME as each row is inserted into the table.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Or, if all things being equal:

DECLARE @DatetimeasVC VARCHAR(30);

set @DatetimeasVC = '10/28/2011 11:47:55.686455 AM';

select convert(datetime,

substring(@DatetimeasVC, 7, 4) + substring(@DatetimeasVC, 1, 2) + substring(@DatetimeasVC, 4, 2) +

substring(stuff(@DatetimeasVC,23,3,''), 11, len(@DatetimeasVC)));

set @DatetimeasVC = '10/28/2011 02:47:55.686455 PM';

select convert(datetime,

substring(@DatetimeasVC, 7, 4) + substring(@DatetimeasVC, 1, 2) + substring(@DatetimeasVC, 4, 2) +

substring(stuff(@DatetimeasVC,23,3,''), 11, len(@DatetimeasVC)));

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 12

Lynn Pettis (3/14/2012)
Or, if all things being equal:
DECLARE @DatetimeasVC VARCHAR(30);
set @DatetimeasVC = '10/28/2011 11:47:55.686455 AM';
select convert(datetime,
substring(@DatetimeasVC, 7, 4) + substring(@DatetimeasVC, 1, 2) + substring(@DatetimeasVC, 4, 2) +
substring(stuff(@DatetimeasVC,23,3,''), 11, len(@DatetimeasVC)));
set @DatetimeasVC = '10/28/2011 02:47:55.686455 PM';
select convert(datetime,
substring(@DatetimeasVC, 7, 4) + substring(@DatetimeasVC, 1, 2) + substring(@DatetimeasVC, 4, 2) +
substring(stuff(@DatetimeasVC,23,3,''), 11, len(@DatetimeasVC)));

There is a bug here, lynn.. Works only for the months of oct, nov and dec ( 2 digit month numbers )

Try this:

set @DatetimeasVC = '9/28/2011 02:47:55.686455 PM';

select convert(datetime,

substring(@DatetimeasVC, 7, 4) + substring(@DatetimeasVC, 1, 2) + substring(@DatetimeasVC, 4, 2) +

substring(stuff(@DatetimeasVC,23,3,''), 11, len(@DatetimeasVC)));

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

ColdCoffee (3/14/2012)
Lynn Pettis (3/14/2012)
Or, if all things being equal:
DECLARE @DatetimeasVC VARCHAR(30);
set @DatetimeasVC = '10/28/2011 11:47:55.686455 AM';
select convert(datetime,
substring(@DatetimeasVC, 7, 4) + substring(@DatetimeasVC, 1, 2) + substring(@DatetimeasVC, 4, 2) +
substring(stuff(@DatetimeasVC,23,3,''), 11, len(@DatetimeasVC)));
set @DatetimeasVC = '10/28/2011 02:47:55.686455 PM';
select convert(datetime,
substring(@DatetimeasVC, 7, 4) + substring(@DatetimeasVC, 1, 2) + substring(@DatetimeasVC, 4, 2) +
substring(stuff(@DatetimeasVC,23,3,''), 11, len(@DatetimeasVC)));
There is a bug here, lynn.. Works only for the months of oct, nov and dec ( 2 digit month numbers )
Try this:
set @DatetimeasVC = '9/28/2011 02:47:55.686455 PM';
select convert(datetime,
substring(@DatetimeasVC, 7, 4) + substring(@DatetimeasVC, 1, 2) + substring(@DatetimeasVC, 4, 2) +
substring(stuff(@DatetimeasVC,23,3,''), 11, len(@DatetimeasVC)));

Maybe, maybe not. Depends on how the data is stored in the string. If leading zeros exist, it should work.

DECLARE @DatetimeasVC VARCHAR(30);

set @DatetimeasVC = '10/28/2011 11:47:55.686455 AM';

select convert(datetime,

substring(@DatetimeasVC, 7, 4) + substring(@DatetimeasVC, 1, 2) + substring(@DatetimeasVC, 4, 2) +

substring(stuff(@DatetimeasVC,23,3,''), 11, len(@DatetimeasVC)));

set @DatetimeasVC = '10/28/2011 02:47:55.686455 PM';

select convert(datetime,

substring(@DatetimeasVC, 7, 4) + substring(@DatetimeasVC, 1, 2) + substring(@DatetimeasVC, 4, 2) +

substring(stuff(@DatetimeasVC,23,3,''), 11, len(@DatetimeasVC)));

set @DatetimeasVC = '09/08/2011 02:47:55.686455 PM';

select convert(datetime,

substring(@DatetimeasVC, 7, 4) + substring(@DatetimeasVC, 1, 2) + substring(@DatetimeasVC, 4, 2) +

substring(stuff(@DatetimeasVC,23,3,''), 11, len(@DatetimeasVC)));

If leading zeros do not exist for months and/or days then it won't work. Looks like an exercise for the OP if that is the case.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 14

How about this?

Sample data

DECLARE @Table TABLE ( DatetimeasVC VARCHAR(30) );

INSERT @Table

SELECT '10/28/2011 11:47:55.686455 AM'

UNION ALL

SELECT '9/28/2011 11:47:55.123455 AM'

UNION ALL

SELECT '11/12/2011 11:47:55.789455 PM'

UNION ALL

SELECT '10/9/2011 11:47:55.9996 AM'

UNION ALL

SELECT '1/26/2011 11:47:55.3456 PM'

UNION ALL

SELECT '1/2/2012 11:47:55.2334455 PM'

And the code:

SELECT DatetimeasVC , CrsApp1.String , CONVERT( DATETIME , CrsApp1.String) [Converted To DateTime]

FROM @Table

CROSS APPLY (SELECT CHARINDEX('.',DatetimeasVC ) ) CrsApp (Idx)

CROSS APPLY (SELECT LEFT ( DatetimeasVC , CrsApp.Idx + 3 ) + ' ' + RIGHT( DatetimeasVC , 2) ) CrsApp1 (String)