DateTime conversion - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

DateTime conversion

Author

Topic

fordrp
Starting Member

2 Posts

Posted - 2010-11-28 : 11:21:46

hi all
what is the most efficient way to convert this string to a datetime value?

'20101115 031100'

Select convert(datetime, '20101115 03:11:00') this works but i would need to insert ":".

thanks ahead of time for your ideas

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 11:39:18

[code]
Declare @stringdate char(24)
Select @stringdate = '20101115 031100'

Select convert(datetime,left(@stringdate,8) + ' '
+ substring(@stringdate,10,2) + ':'
+ substring(@stringdate,12,2) + ':'
+ substring(@stringdate,14,2) + '.000')
[/code]

Poor planning on your part does not constitute an emergency on my part.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-28 : 12:08:22

I would use a stuff to insert the :'s.
Doesn't need the .000 at the end but doesn't hurt.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-28 : 12:18:30

is this value coming from table field? if yes why is it not declared as datetime? please try to use proper datatype for fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 13:10:27

Stuff works as well..good points. Perhaps the OP is getting external data and needs to convert it into his properly stored data.


Declare @s  char(24)
Select @s  = '20101115 031100'

Select convert(datetime,STUFF(STUFF(@s,12,0,':'),15,0,':') )

Poor planning on your part does not constitute an emergency on my part.

fordrp
Starting Member

2 Posts

Posted - 2010-11-29 : 13:43:53

Thanks everyone... i incorporated the stuff syntax into my view and it works great...

As was alluded to my need to convert is based on a flat file i recieve from an external source and i need to load it as a proper datetime field in my db.

thanks again !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources