How to convert hhmm into datetime 13:30:00 T-SQL?

Question

Post reply

How to convert hhmm into datetime 13:30:00 T-SQL?

pickgoods

SSC-Addicted

Points: 472
More actions
December 2, 2008 at 3:42 pm

#132535

Converting varchar to datetime T-SQL.
Hi, experts
How do I convert a varchar hhmm into a datetime 13:30:00 if the varchar hhmm = 1330?
Thank you.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

That's not really a DateTime datatype you asked for or it would have a date with it like this...

DECLARE @Time CHAR(4)

SET @Time = '1330'

SELECT CAST(STUFF(@Time,3,0,':') AS DATETIME)

If you're just trying to display it differently (best to do formatting in the GUI, if you have one), then this would work...

DECLARE @Time CHAR(4)

SET @Time = '1330'

SELECT STUFF(@Time,3,0,':') + ':00'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 2

If times are AM, use

STUFF(REPLACE(STR(Col1, 4), ' ', '0'), 3, 0, ':')

N 56°04'39.16"
E 12°55'05.25"

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

pickgoods (12/2/2008)
Converting varchar to datetime T-SQL.
Hi, experts
How do I convert a varchar hhmm into a datetime 13:30:00 if the varchar hhmm = 1330?
Thank you.

Jeff's answer will work only if you guarantee that you'll have 4 digits regardless of the value. For example if the time should be 9:05 will you varchar show 0905?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Deeptiprasad SSC Eights! Points: 995 More actions · Answer 4

declare @nvsDateTime NVARCHAR(20)

set @nvsDateTime = '1330'

select REVERSE(SUBSTRING(REVERSE(@nvsDateTime),0,3) + ':' + SUBSTRING(REVERSE(@nvsDateTime),3,LEN(REVERSE(@nvsDateTime))))

try this.....

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 5

Which is easier than STUFF(REPLACE(STR(Col1, 4), ' ', '0'), 3, 0, ':')

N 56°04'39.16"
E 12°55'05.25"

pickgoods SSC-Addicted Points: 472 More actions · Answer 6

Thank you all of you.

They are very helpful. Especially this one: STUFF(REPLACE(STR('1416', 4), ' ', '0'), 3, 0, ':')

Chethan Srinivas Shetty SSC Journeyman Points: 97 More actions · Answer 7

Thanks for enlightening me on the use of reverse and substring. Is a flexible way of manipulating string formats into numbers. cheers

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Chethan Srinivas Shetty (7/21/2009)
Thanks for enlightening me on the use of reverse and substring. Is a flexible way of manipulating string formats into numbers. cheers

Just be careful, Chethan... REVERSE is a fairly expensive function and doing something like using 4 reverses in a function can really cause a bit of a drag on the CPU if you have to process a lot of rows. Correctly STUFFing a string like Peso did is much quicker.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions