Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How to convert from INT to DATETIME?

Posted on 2001-09-12
Medium Priority
1,067 Views
How do I convert an INT to DATETIME?
0
Question by:yurrea
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4
• 3
• +3

Author Comment

ID: 6478569
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

LVL 1

Expert Comment

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

Author Comment

ID: 6478587
kdq2000:

how do you do it in me second comment?
0

LVL 1

Expert Comment

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

LVL 1

Expert Comment

ID: 6478597
Sept. 13, 2001 is number 37145
0

Author Comment

ID: 6478619
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

LVL 3

Expert Comment

ID: 6478657
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

LVL 1

Expert Comment

ID: 6478679
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

LVL 143

Expert Comment

ID: 6479145
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

LVL 3

Expert Comment

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

LVL 143

Expert Comment

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

LVL 9

Expert Comment

ID: 6479426
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 Comment

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

Yvann
0

Author Comment

ID: 6481347
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

LVL 3

Expert Comment

ID: 6481981
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

LVL 9

Expert Comment

ID: 6482248
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

LVL 1

Expert Comment

ID: 6482493
0

LVL 1

Accepted Solution

george74 earned 200 total points
ID: 6482497
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

LVL 1

Expert Comment

ID: 6482499
(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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦