Problem casting String to SQL Server Datetime

Question

Hi All,

I have a string in the following format which I am trying to convert to SQL Server datetime.

yyyymmdd24miss

Eg. 20091102165659

I have tried casting using the following statement:

Select cast (20091102165659 as datetime)
Error received - Arithmetic overflow error converting expression to data type datetime.

The destination field in SQL Server is set to standard datetime.

Any assistance would be really appreciated.

Regards,
Chris

Answer 1

Chris, you need convert to varchar first and prepare the string to know convert format.

Something like this:

Select Cast(Stuff(Stuff(Stuff(Stuff(Stuff('20091102165659', 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0, ':') as DateTime)

---

Tks. Fausto Fiorese Branco DBA - SQL Server 2k5 São Paulo - Brasil * http://www.linkedin.com/in/faustobranco

Answer 2

you can also get with three STUFF functions

SELECT

 

CAST(STUFF(STUFF(STUFF('20091102165659', 9, 0, ' '), 12, 0, ':'), 15, 0, ':') AS DATETIME)

---

Rajesh Jonnalagadda http://www.ggktech.com

Answer 3

Hi Fausto,

This worked! Many Thanks all for your assistance.

Select Cast(Stuff(Stuff(Stuff(Stuff(Stuff('20091102165659', 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0, ':') as DateTime)

Regards,
Chris