• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1091

How to convert from INT to DATETIME?

How do I convert an INT to DATETIME?
0
yurrea
• 5
• 4
• 3
• +3
1 Solution

Author Commented:
Suppose that I have the date and time in two integer variables

DECLARE @Date INT
DECLARE @Time INT

SET @Date = 20010913  // Sept. 13, 2001
SET @Time = 143010  // 2:30:10 PM

.. How do I merge these two into a single DATETIME variable?
0

Commented:
declare @q as int
set @q=37145
select cast(@q as smalldatetime)
0

Author Commented:
kdq2000:

how do you do it in me second comment?
0

Commented:
declare @q as int
set @q=37145
select cast(@q as smalldatetime)
0

Commented:
Sept. 13, 2001 is number 37145
0

Author Commented:
kdq2000:

Suppose that I have as my data the following:

SET @Date = 20010913 // yyyymmdd
SET @Time = 143010 // hhmmss

how do I convert these two variables to DATETIME?
0

Commented:
declare @Date int, @Time int
SET @Date = 20010913
SET @Time = 143010

select convert(datetime,
substring( convert(char(8), @Date ), 1,4 )+'-'+
substring( convert(char(8), @Date ), 5,2 )+'-'+
substring( convert(char(8), @Date ), 7,2 )+' '+
substring( convert(char(6), @Time ), 1,2 )+':'+
substring( convert(char(6), @Time ), 3,2 )+':'+
substring( convert(char(6), @Time ), 5,2 ), 120)

probably not the best way, but it will work :)
0

Commented:
yurrea,

here it goes. @DT will hold the resulting datetime.

Cheers.

declare @Date int
declare @Time int

SET @Date = 20010913
SET @Time = 143010

declare @DT datetime
set @DT = cast(stuff(stuff(cast(@Date as varchar), 5, 0, '/'), 8, 0, '/') + ' ' + stuff(stuff(cast(@Time as varchar), 3, 0, ':'), 6, 0, ':') as datetime)
print @DT
0

Billing EngineerCommented:
In fact, it will be much easier, using the convert function, but i have to check the parameters...

it will be like this:

select convert ( datetime , cast( @date as char(9)) + cast ( @time as char(6)) , XXX )

The value XXX must be replaced by the value that convert would use to produce a char representation of a datetime that looks like YYYYMMDD HHMISS

Cheers
0

Commented:
angel, i checked the BOL. there is no such format (YYYYMMDD HHMI)described for convert (cast) function.
0

Billing EngineerCommented:
ibro, you are correct...
in fact, only the year part exists without separator (ie 112)
Sorry.
0

Commented:
I have exactly opposite idea ( meaning, this is the way I perceive it, but it just one more way )
leave the int(s) as they are and make the dates to become int(s). yurrea, will you agree that it is not critical, whether the int is converted to date, or date is to int. It is performance and the logic is important. Otherwise, just ignore the following. This is on my view close to the one beaming in the background of angellll's solution - no separators and the value is numerical.

datepart( year, <column_name> ) * 10000 + datepart( month, <column_name> ) * 100 + datepart( day, <column_name> ) will get the YYYYMMDD
and
datepart( hour, <column_name> ) * 10000 + datepart( mi, <column_name> ) * 100 + datepart( ss, <column_name> )
gets the HHMMSS
all of the values can be stuffed into BIGINT and dealt with as an int, arithmetical calculations usually faster than string manipulation, and less CPU intencive, especially for large range for selects. Also, this way the values can be stored as int(s) or BIGINT(s) in a database, and provide for quick comparison.
0

Author Commented:
I guess you have a point there miron.  Thanks for letting me know. :)

Yvann
0

Author Commented:
hey guys! ... I don't know which answer to accept. .. the other guys answered my question, but miron gave an alternative which I intend to use.  However, ibro and george74 though they have similar logic have also addressed my problem, and intend to use them also in some of my future tasks. What do you think?

Yvann
0

Commented:
yurrea,
just i want to let you know that miron's suggestions has traps. Using int (bigint) for datetime values can be dangerous and can make your code hell in some sitiations. Let me just mentions some of possible cases:
1. validitity of data. Obviously you can store some invalid date because of front-end problems (bugs) or within your sql code. How are you gonna interpret and fix them. Say that you store 100 in the data field. How are you gonna understand it.
2. Invalid dates  - for example date 20010229 is invalid,
but 20000229 is valid cause some years february has not 28, but 29 days.
3. Date arithmetic - if you want to find how many days are between 20010201 and 20010301 you have to make some complex calculation, because some months has 30, some 31 some 28 and even 29 days.
4. If you want to find if some day is weekday or weekend, or which day of week is it, you have to convert your int value to datetime anyway.
5. In your front-end applicaiton it will be very difficult to show the datetime in different formats if you work with int. You again you have to conver it to datetime equivalent and after that use the proper date format.
6. If in your front-end application you gather data in integer format, better convert it to datetime value in front end and send it as datetime (string also will work fine) and then send it db. Most of popular front-ends (VB, Java, C/C++) have good methods (functions) for that conversation.
7.  cases can be extended if you think a little bit more about possible problems.

My general idea is not to go for this variant of storing int as datetime values in database. If you use directly datetime (smalldatetime) data type in database, you will be able to use much more features. Why  do you think this data type was introduced in db level anyway?
0

Commented:
The concerns ibpro raised sound prudent. But they are beyond the scope of the question on one hand and mix together apples and oranges on the other.

My proposal focused on the data comparison routine. And the routine is more robust than the one using string manipulation. Built on an assumption that the source of data has taken care of data hygiene / validation, and if the source of data can pass to data comparison routine invalid data - than this may not be fixed by mere adhering to a specific data type. It will take design of a whole separate routine. The need of such routine may dictate use of datetime datatype, but again - there will be no advantage to string manipulation routine. String manipulation routines are good for places where strings suite well - output of dates to web pages, string values manipulation in the database. In this case use of numerical data is by far more advantageous. I see no single point from the engineering view to use string manipulation in this case.

As to the reasons of insufficiency of BIGINT for storing dates, I would like to hear more strong argument, then that datetime is devised to store it. It is fortunate that programming is young. We are still designing data types, and just recently got int64 / bigint. Deferring its advantages means depriving ourselves from more robust / better performing algorithms. Not using its advantages is a sacrifice for no reason.
0

Commented:
0

Commented:
hmmmm... no text??? ok, again

talking about being out of scope, the question was "How do I convert an INT to DATETIME?" and not "how do i convert datetime to int" ;)

otherwise i did performance tests on the conversion routine and there is about a 2.1:4.2:3.4 ratio between the 3 algorithms (miron:ibro:myself). this shows that mirons algorithm is really the best performing, except that it converts the other way than needed.

and having yurrea wanting to convert his int values into datetime gives the idea that he has his dates stored as datetime, he receives the int values as user input or something.
therefore most likely miron's routine would run only once - assuming that yurrea would like to work without the added value of datetime datatype which is exactly what ibro highlighted precisely (data checks, comparison functions, increment functions, day of week functions) - and would convert his existing datetime columns into bigint ones.

it is fortunate that hardware prices are going down and we can afford to use "slow" routines that however result a cleaner and more logical code (imagine you write your own day-of-the-week based on bigint type dates, your own dateadd functions (dealing with days of months and calendar adjustments).... or converting all your bigints to datetime before using any of these functions....

0

Commented:
(so it was probably too long for this webform...)

for performance test, feel free to conduct on your own:

set nocount on
create table #tbdd(tsd int)
go
create table #tbdi(tsd int)
go
create table #tbdd2(tsd int)
go

declare @od datetime     -- original date as datetime
declare @oid int     -- original date as int
declare @oit int     -- original tima as int

declare @md datetime     -- "my date" as datetime
declare @mid int     -- "my date" as int
declare @mit int     -- "my time" as int

-- get current date
set @od = getdate()
set @oid = datepart( year, @od ) * 10000 + datepart( month, @od ) * 100 + datepart( day, @od)
set @oit = datepart( hour, @od ) * 10000 + datepart( mi, @od ) * 100 + datepart( ss, @od)

declare @tss datetime     -- conversion block start time
declare @tse datetime     -- conversion block end time

declare @iMax int
declare @jMax int

declare @i int
declare @j int
set @i = 0
set @iMax = 1000
set @jMax = 1000
while @i < @iMax
begin
set @j = 0
set @tss = current_timestamp
while @j < @jMax
begin
set @mid = datepart( year, @od ) * 10000 + datepart( month, @od ) * 100 + datepart( day, @od)
set @mit = datepart( hour, @od ) * 10000 + datepart( mi, @od ) * 100 + datepart( ss, @od)
set @j = @j + 1
end
set @tse = current_timestamp
insert into #tbdi(tsd) values(datediff(ms, @tss, @tse))
set @i = @i+1
end

set @i = 0
while @i < @iMax
begin
set @j = 0
set @tss = current_timestamp
while @j <@jMax
begin
set @md = convert(datetime,
substring( convert(char(8), @oid ), 1,4 )+'-'+
substring( convert(char(8), @oid ), 5,2 )+'-'+
substring( convert(char(8), @oid ), 7,2 )+' '+
substring( convert(char(6), @oit ), 1,2 )+':'+
substring( convert(char(6), @oit ), 3,2 )+':'+
substring( convert(char(6), @oit ), 5,2 ), 120)
set @j=@j+1
end
set @tse = current_timestamp
insert into #tbdd2(tsd) values(datediff(ms, @tss, @tse))
set @i = @i+1
end

set @i = 0
while @i < @iMax
begin
set @j = 0
set @tss = current_timestamp
while @j <@jMax
begin
set @md = cast(stuff(stuff(cast(@oid as varchar), 5, 0, '/'), 8, 0, '/') + ' ' + stuff(stuff(cast(@oit as varchar), 3, 0, ':'), 6, 0, ':') as datetime)
set @j=@j+1
end
set @tse = current_timestamp
insert into #tbdd(tsd) values(datediff(ms, @tss, @tse))
set @i = @i+1
end

go
select avg(tsd) as 'avg. ms diff working with integers-miron' from #tbdi
select avg(tsd) as 'avg. ms diff working with strings-ibro' from #tbdd2
select avg(tsd) as 'avg. ms diff working with strings-george74' from #tbdd
go

drop table #tbdi
go
drop table #tbdd
go
drop table #tbdd2
go
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.