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
19
Medium Priority
?
1,067 Views
Last Modified: 2007-11-27
How do I convert an INT to DATETIME?
0
Comment
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
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3
19 Comments
 

Author Comment

by:yurrea
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

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

Author Comment

by:yurrea
ID: 6478587
kdq2000:

how do you do it in me second comment?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 1

Expert Comment

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

Expert Comment

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

Author Comment

by:yurrea
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

by:ibro
ID: 6478657
how about this:
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

by:george74
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

by:Guy Hengel [angelIII / a3]
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

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6479210
ibro, you are correct...
in fact, only the year part exists without separator (ie 112)
Sorry.
0
 
LVL 9

Expert Comment

by:miron
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

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

Yvann
0
 

Author Comment

by:yurrea
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

by:ibro
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

by:miron
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

by:george74
ID: 6482493
0
 
LVL 1

Accepted Solution

by:
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

by:george74
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question