?
Solved

Datetime to int

Posted on 2011-03-15
20
Medium Priority
?
563 Views
Last Modified: 2012-05-11
Hi,
Below is a statement that will convert a datetime value to a integer, in this example the integer is '36902'
If I change the time slightly in the statement the result is the same '36902'
How can I produce a unique integer when just the time changes?

Thank you

Print Cast(convert(datetime, '12-Jan-2001 13:06:15') As int)

Open in new window

0
Comment
Question by:crompnk
[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
  • 4
  • 4
  • 4
  • +6
20 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35138651

You cant since the time portion is float and it is truncated

So try using a float instead
0
 
LVL 18

Accepted Solution

by:
deighton earned 1000 total points
ID: 35138654
Print cast(Cast(convert(datetime, '12-Jan-2001 13:06:15') As float) * 60 * 3600 as BIGINT)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35138669
the CAST as INT will return the integer in terms of days passed since the "date 0" (aka 1900-01-01)

so, integer cannot be used to refer to the time portion.
decimal could do that, but I wonder why you try to do this?

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 18

Expert Comment

by:deighton
ID: 35138685
sorry it would be

print cast(Cast(convert(datetime, '12-Jan-2001 13:06:18') As float) * 24 * 3600 as BIGINT)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35138727
for greater precision use Numeric:

Print Cast(convert(datetime, '12-Jan-2001 14:06:15') As numeric(15,10))
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 35138842
or this

declare @myDate1 datetime = convert(datetime, '12-Jan-2001 13:06:15')
declare @myDate2 datetime = convert(datetime, '12-Jan-2001 13:06:16')

print cast(@mydate1 as bigint)*24*60*60 + datepart(hour,@mydate1)*3600 + datepart(minute,@mydate1)*60 + datepart(second,@mydate1)
print cast(@mydate2 as bigint)*24*60*60 + datepart(hour,@mydate2)*3600 + datepart(minute,@mydate2)*60 + datepart(second,@mydate2)

idea is calculate seconds passed for a given date
0
 
LVL 6

Expert Comment

by:Mistralol
ID: 35139108

Try this instead

Convert datetime to int
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 35139184
here is what posted so far
declare @myDate1 datetime = convert(datetime, '12-Jan-2001 13:06:15')
declare @myDate2 datetime = convert(datetime, '12-Jan-2001 13:06:16')

Print cast(Cast(@mydate1 As float)*24*3600 as bigint)
Print cast(Cast(@mydate2 As float)*24*3600 as bigint)

print cast(@mydate1 as bigint)*24*60*60 + datepart(hour,@mydate1)*3600 + datepart(minute,@mydate1)*60 + datepart(second,@mydate1)
print cast(@mydate2 as bigint)*24*60*60 + datepart(hour,@mydate2)*3600 + datepart(minute,@mydate2)*60 + datepart(second,@mydate2)

print DATEDIFF(second, '1970/01/01 00:00:00', @mydate1)
print DATEDIFF(second, '1970/01/01 00:00:00', @mydate2)

3188293575
3188293576
3188379975
3188379976
979304775
979304776

Open in new window

0
 
LVL 57

Expert Comment

by:HainKurt
ID: 35139229
missed KnightEknight
declare @myDate1 datetime = convert(datetime, '12-Jan-2001 13:06:15')
declare @myDate2 datetime = convert(datetime, '12-Jan-2001 13:06:16')

Print 'By deighton'
Print cast(Cast(@mydate1 As float)*24*3600 as bigint)
Print cast(Cast(@mydate2 As float)*24*3600 as bigint)

Print 'By Me :)'
print cast(@mydate1 as bigint)*24*60*60 + datepart(hour,@mydate1)*3600 + datepart(minute,@mydate1)*60 + datepart(second,@mydate1)
print cast(@mydate2 as bigint)*24*60*60 + datepart(hour,@mydate2)*3600 + datepart(minute,@mydate2)*60 + datepart(second,@mydate2)

Print 'By MistralolDate'
print DATEDIFF(second, '1970/01/01 00:00:00', @mydate1)
print DATEDIFF(second, '1970/01/01 00:00:00', @mydate2)

Print 'By knightEknight'
Print Cast(@mydate1 As numeric(15,10)) 
Print Cast(@mydate2 As numeric(15,10)) 

By deighton
3188293575
3188293576

By Me :)
3188379975
3188379976

By MistralolDate
979304775
979304776

By knightEknight
36901.5460069444
36901.5460185185

Open in new window

0
 
LVL 6

Expert Comment

by:Mistralol
ID: 35139259

Which is stupidly complex compared to

SELECT DATEDIFF(second, '1970/01/01 00:00:00', GETDATE())

So if you are using a base time of 2000/01/01 00:00:00 then change it :)

If your using an int for a time then its going to be offset of a constant date from somewhere.
If its in seconds use the above if its in hours then switch our seconds for hours etc....

Without knowing more what the int represent it is actually impossible to answer the question
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 35139316
looks like you are new here: I did not like your sentence "stupidly complex"!
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35139337
HainKurt simply summarized all of the suggestions in one post -- it wasn't a "stupidly complex" answer, it was a demo of all of the posted suggestions.
0
 
LVL 6

Expert Comment

by:Mistralol
ID: 35139366

I don't really care if you didn't like it or not.

It is a simpler / cleaner solution based on the original poster's question based on the very limited information provider. I say let the original poster choose or provide more information as see what solution is selected :)



0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35139477
No one has criticized your suggestion, it is perfectly good.  It's your attitude that needs work.

Again, have a closer look at HainKurt's last summary post.  He's doing you a favor.
0
 
LVL 18

Expert Comment

by:deighton
ID: 35139568
Mistralol, try putting 2050 into your formula!

We are edging closer to a glitch that I first heard about in the 1990s, I think it will hit by  2039, integer cannot hold the UNIX time, and of course many systems, such as financial stock systems work on dates 20+ years into the future.  

Also DateDiff cannot handle big differences with seconds, it blows up
0
 

Author Comment

by:crompnk
ID: 35139597
Hi,
Thanks for the feedback, I will test the solutions and see, which works best for me.
The reason I am trying to do this is because I have one primary key field, lets call it 'ID'
The ID field has to be unique, I want to concatenate another field (code i.e. 'A') and the datetime value to make the record unique, I will then store the real date in another field. Only the ID field can be the primary key in this table, I didn't want to concatenate the actual datetime as this would be too long and messy:

CODE       DATETIME                           ID
A              12-Jan-2001 13:06:15        A_3188293575
A              12-Jan-2001 13:06:16        A_3188293576
B              12-Jan-2001 13:06:15        B_3188293575
B              12-Jan-2001 13:06:16        B_3188293576

It would be good if the ID field could be queried and the numeric equivalent suffix converted back to the original datetime.

Thanks
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 35140417
Instead of having a big number as ID, why don't you generate a row number and use that as your primary key.
You can try like this.
select Code,[DateTime],Code + convert(varchar,rn) as ID
  from (select *,row_number() over (partition  by Code order by [DateTime]) rn 
          from your_table) t1

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35143490
>>I don't really care if you didn't like it or not.<<
If you A) cannot produce a solution that satisfies the requirements of a SQL Server base date as opposed to a UNIX base date (1980 vs 1970) and B) are incapable of showing any manners than perhaps EE is not the right community for you, so you should do yourself a favor and go somewhere else.

0
 
LVL 18

Expert Comment

by:deighton
ID: 35146326
hey I think Mistralol should be welcome to stay!  Everyone has their own way of saying things!  

sometimes I do make code complex, I've been doing it for 30 years and it might not seem complex to me -  but in this case it is for a reason, datediff seconds flunks for large spans of years.

It's when people send EMails to my boss attacking my code I start to feel a bit heated, but not here, lol.
0
 
LVL 6

Expert Comment

by:Mistralol
ID: 35146401

Hey thanks for that :)

I was pissed at the fact that the different answers had been aggregated in a possible way to steal points etc..

The unix timestamp has almost nothing todo with things here. The only thing that makes a unix timestamp a unix timestamp is because its the number of seconds since the last epoch. It just happens to have the perfect way to convert a datetime to an int however :)

If your going to store a datetime as an int then sooner or later your going to overflow :) But it depends when you overflow based on when the base date is from (eg 1970 ... could just as easy be 2000 ...) this extends well past the year 2038. But the timestamp could also be measured in minutes or hours from a defined base date this would again greatly reduce the risk of overflowing so soon.

I was really leaving that part up to the OP to figure out exactly what he needed since there isn't enough information to a make a decent solution out of it. Not to mention that building something based on datetime to be unique is probably also a bad idea :)
0

Featured Post

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

777 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