Solved

How to convert from INT to DATETIME?

Posted on 2001-09-12
19
1,052 Views
Last Modified: 2007-11-27
How do I convert an INT to DATETIME?
0
Comment
Question by:yurrea
  • 5
  • 4
  • 3
  • +3
19 Comments
 

Author Comment

by:yurrea
Comment Utility
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
Comment Utility
declare @q as int
set @q=37145
select cast(@q as smalldatetime)
0
 

Author Comment

by:yurrea
Comment Utility
kdq2000:

how do you do it in me second comment?
0
 
LVL 1

Expert Comment

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

Expert Comment

by:kdg2000
Comment Utility
Sept. 13, 2001 is number 37145
0
 

Author Comment

by:yurrea
Comment Utility
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
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 3

Expert Comment

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

Expert Comment

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

Expert Comment

by:miron
Comment Utility
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
Comment Utility
I guess you have a point there miron.  Thanks for letting me know. :)

Yvann
0
 

Author Comment

by:yurrea
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 1

Accepted Solution

by:
george74 earned 50 total points
Comment Utility
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
Comment Utility
(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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now