Click here to Skip to main content
15,176,823 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a database that contain date column and time column..both are on INT type column.

Date
20160515
20160415
20160315
20160215
20160115
Time
190101
200101
210101
220101
230101

Now, I would like to combine these two column to be one column that will be define on queries. Right now, I have queries to convert both date and time into separate result. How to combine it?

C#
SELECT CAST(STUFF(STUFF(STUFF(RIGHT('00000000' + 
CAST(190101 AS VARCHAR),8),3,0,':'),6,0,':'),9,0,'.') AS TIME) as sqltime

Select Convert(DATETIME, LEFT(20160515, 8)) as currdate


**** This SQL will be use as CommandText on Visual Studio programming function for sending data to another database
This SQL is testing directly on MSSQL Management Studio Queries before append to code.

What I have tried:

SQL:INTEGER TO 24H FORMAT
-------------------------
SELECT CAST(STUFF(STUFF(STUFF(RIGHT('00000000' +
CAST(190101 AS VARCHAR),8),3,0,':'),6,0,':'),9,0,'.') AS TIME) as sqltime

SQL: INTEGER TO 24H FORMAT INCLUDE MILLISECONDS
-----------------------------------------------
select dateadd(hour, (190101 / 1000000) % 100,
dateadd(minute, (190101 / 10000) % 100,
dateadd(second, (190101 / 100) % 100,
dateadd(millisecond, (190101 % 100) * 10, cast('00:00:00' as time(2))))))

SQL: INTEGER TO DATE
--------------------
Select Convert(DATETIME, LEFT(20130101, 8)) as currdate
Posted
Updated 19-Aug-16 0:49am

1 solution

There's few ways to achieve that. For example:
SQL
--uncomment below lines to return datetime
--SELECT CONVERT(DATETIME, CONCAT(NewDate, ' ', STUFF(STUFF(NewTime, 3, 0, ':'), 6, 0, ':'))) AS NewDate
--FROM (
	SELECT CONVERT(VARCHAR(10), [Date]) AS NewDate, RIGHT('000000' + CAST([Time] AS VARCHAR(6)), 6) AS NewTime
	FROM @tmp
--) AS t


Returns:
2016-05-15 09:59:59.000
2016-04-15 00:00:01.000
2016-05-15 19:01:01.000
2016-04-15 20:01:01.000
2016-03-15 21:01:01.000
2016-02-15 22:01:01.000
2016-01-15 23:01:01.000


More at: CAST and CONVERT (Transact-SQL)[^]
   
v3
Comments
Luiey Ichigo 19-Aug-16 21:12pm
   
Hi Maciej..

This queries only works on 6 digits number as minimum 10:00:00am which will be 100000. But if the time is 09:59:59 and the value in the row is 095959 or 95959, SQL will return error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value"

Because my client convert 24hours format time to number which if current time is 00:00:01 (12:00:01PM 12Hr format) the data on row will be 1 only.

This statement is works to convert it back to time:-
SELECT CAST(STUFF(STUFF(STUFF(RIGHT('00000000' +
CAST(101 AS VARCHAR),8),3,0,':'),6,0,':'),9,0,'.') AS TIME) as sqltime

How do I merge this time convert and date convert to be as datetime data?
Maciej Los 20-Aug-16 4:06am
   
See updated answer ;)
Luiey Ichigo 22-Aug-16 2:36am
   
Great..thanks!
Maciej Los 22-Aug-16 2:56am
   
You're very welcome.
Cheers,
Maciej

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900