how can i convert 'yyyymmddhhmmss' to datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how can i convert 'yyyymmddhhmmss' to datetime

Author

Topic

jeremyzb
Starting Member

2 Posts

Posted - 2010-07-15 : 00:06:48

Hi, there

How can I convert string like '20090513024903' to datetime?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-15 : 00:08:38

[code]
declare @s varchar(20)

select @s = '20090513024903'

select convert(datetime, stuff(stuff(stuff(@s, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'))
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jeremyzb
Starting Member

2 Posts

Posted - 2010-07-15 : 00:36:53

to khtan,

thx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-15 : 00:38:48

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-01-21 : 03:13:09

what if there are alot of data.

how can i convert it?

i cant set every of each value.

kindly advice, thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 06:30:41

quote:
Originally posted by peace

what if there are alot of data.

how can i convert it?

i cant set every of each value.

kindly advice, thanks

that was just for illustration
if its table field you can simply do this


select	convert(datetime, stuff(stuff(stuff(columnname, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'))
from tablename

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-01-21 : 20:58:38

im getting this error as my originally datatype is nvarchar.

Conversion failed when converting datetime from character string.

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-01-21 : 21:02:07

im getting this error as my originally datatype is nvarchar.

Conversion failed when converting datetime from character string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 08:22:38

quote:
Originally posted by peace

im getting this error as my originally datatype is nvarchar.

Conversion failed when converting datetime from character string.

thats because either format of date values in the column is not consistent or you've some spurios values
what does below return?


select columnname
from table
where isdate(left(columnname,8))=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-01-27 : 02:41:17

quote:
Originally posted by visakh16

quote:
Originally posted by peace

im getting this error as my originally datatype is nvarchar.

Conversion failed when converting datetime from character string.

thats because either format of date values in the column is not consistent or you've some spurios values
what does below return?

select columnname
from table
where isdate(left(columnname,8))=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

The problem might be in time part as well

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources