Converting Integer date & time into datetime variable .. help!

Question

Post reply

Converting Integer date & time into datetime variable .. help!

RenzoSQL

Ten Centuries

Points: 1252
More actions
June 27, 2013 at 2:43 pm

#295202

Hi!
I have two fields that are integer type in this format
DATE: 1130627 = (exclude the first digit)(13-06-27)
TIME: 51458 (24hr) = 5:14:58
I am trying to get this into a single datetime field and I cant figure it out 🙁
Can anyone help please.
Thanks!
R.

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

This is not beautiful but might do the trick while I try something different.

DECLARE @Date int = 1130627 ,--= (exclude the first digit)(13-06-27)

@Time int = 51458 --(24hr) = 5:14:58

SELECT DATEADD( ss, @Time % 100, DATEADD( minute, (@Time % 10000) / 100, DATEADD( hh, @Time / 10000, CONVERT( datetime, RIGHT( @Date, 6),12))))

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

And here's a different approach.

SELECT CONVERT( datetime, RIGHT( @Date, 6) + ' ' + STUFF( STUFF( RIGHT( '0' + CAST( @Time as varchar(6)), 6), 5,0,':'), 3,0,':'), 12)

If you have any questions on what's happening on any of the solutions, feel free to ask.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

RenzoSQL Ten Centuries Points: 1252 More actions · Answer 3

RenzoSQL

Ten Centuries

Points: 1252

June 27, 2013 at 3:41 pm

#1628149

Thank you so much! 🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Similar to the one Luis posted:

DECLARE @ThisDate INT,

@ThisTime INT;

SET @ThisDate = 1130627;

SET @ThisTime = 51458;

SELECT CAST(CAST(19000000 + @ThisDate AS VARCHAR) + ' ' + STUFF(STUFF(RIGHT('0' + CAST(@ThisTime AS VARCHAR), 6),5,0,':'),3,0,':') AS DATETIME);

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

Since nobody has said it yet....any chance you can change your datatype in the table to be a datetime? You really should be storing datetime information in a datetime column. It avoid all the hurdles you are going through now.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

I understood that he wanted the code to fill a datetime column instead of using the int columns. I might be wrong :hehe:

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

Luis Cazares (6/28/2013)
I understood that he wanted the code to fill a datetime column instead of using the int columns. I might be wrong :hehe:

It very well could be that the OP is already trying to make their life easier by following my suggestion long before I said it. 😎

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dwain Camps SSC Guru Points: 86908 More actions · Answer 8

Another way:

DECLARE @Date int = 1130627 ,--= (exclude the first digit)(13-06-27)

@Time int = 51458 --(24hr) = 5:14:58

SELECT DATEADD(hour, @Time/10000

,DATEADD(minute, @Time/100%100

,DATEADD(second, @Time%100, STUFF(STUFF(@Date+19000000, 7, 0, '-'), 5, 0, '-'))))

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St