Stuff it - 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

Stuff it

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-06-05 : 14:26:29

i know i've dealt with this before but just can't find the info.

I have an column (GCSORECEIVETIME) that is populated with four digit data that looks like '1103' and similar. I need to turn it into '11:03:00:000'. The field that will be taking that is a datetime field and the GCSORECEIVETIME field is a varchar(4).

what IS the syntax for making that conversion??

thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-05 : 14:33:26

SELECT LEFT(GCSORECEIVETIME, 2) + ':' + RIGHT(GCSORECEIVETIME, 2) + ':00:000'
FROM ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-06-05 : 14:37:43

Sweet. Thank U!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-05 : 14:38:07

select stuff('1103',3,0,':')

Jim

Everyday I learn something that somebody else already knew

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-06-05 : 14:56:15

more than one way to skin a cat, as usual. thanks! <----hates cats.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 16:15:37

[code]SELECT CAST(DATEADD(hour,GCSORECEIVETIME%100,DATEADD(minute,GCSORECEIVETIME/100,0)) AS Time) FROM Table[/code]

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

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-05 : 16:56:41

quote:
Originally posted by visakh16

SELECT CAST(DATEADD(hour,GCSORECEIVETIME%100,DATEADD(minute,GCSORECEIVETIME/100,0)) AS Time) FROM Table

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

[/quote]



you wrongly calculated .I have corrected it to produce right result 

SELECT CAST(DATEADD(HOUR,1103/100,DATEADD(MINUTE,1103%100,0)) AS Time)

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 18:12:45

ah
thanks for the catch
time to grab some coffee

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources