convert to datetime - 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

SQL Server 2008 Forums

Transact-SQL (2008)

convert to datetime

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-02-03 : 07:47:49

how i convert this data to datetime format

02022012 0332 ->02.02.2012 03:32:00
02022012 0347 ->02.02.2012 03:47:00
02022012 0402 ->02.02.2012 04:02:00

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-02-03 : 09:48:24

Heres one way:


declare @stringdate varchar(15)

set @stringdate = '02022012 0332'

select @stringdate, convert(datetime,substring(@stringdate,5,4) + '-' + substring(@stringdate,3,2) + '-' + substring(@stringdate,1,2) + ' ' + substring(@stringdate,10,2) + ':' + substring(@stringdate,12,2))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 10:01:26

you need to use STUFF

STUFF(STUFF(STUFF(datefield,3,0,'.'),5,0,'.'),13,0,':'),16,0,':00')

didnt understand why you're storing dates in non datetime columns though!

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-03 : 10:12:59

[code]DECLARE @Sample VARCHAR(15) = '02022012 0332'

SELECT @Sample AS Original,
STUFF(STUFF(STUFF(@Sample, 12, 0, ':'), 5, 0, '.'), 3, 0,'.') + ':00' AS SwePeso[/code]

N 56°04'39.26"
E 12°55'05.63"

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-02-06 : 14:16:41

i get this format from my suppliers, and i want to convert to datetime

quote:
Originally posted by visakh16

you need to use STUFF

STUFF(STUFF(STUFF(datefield,3,0,'.'),5,0,'.'),13,0,':'),16,0,':00')

didnt understand why you're storing dates in non datetime columns though!

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 14:56:54

quote:
Originally posted by inbs

i get this format from my suppliers, and i want to convert to datetime
quote:
Originally posted by visakh16

you need to use STUFF

STUFF(STUFF(STUFF(datefield,3,0,'.'),5,0,'.'),13,0,':'),16,0,':00')

didnt understand why you're storing dates in non datetime columns though!

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

which format?
for converting to date use CONVERT

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources