• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

How do Return a unique number from a datetime field in sql 2008

Hi Gurus

I am pulling my hair out.  How do i return an integer that is unique based on date AND time from a date time field.  The value currently in the datetime field which is called datetimsubmitted is '2010/09/03 10:18:05 AM'

It is easy to return an integer from the date only but each record needs to be uniquely identified by an integer based on date and time to the second
many thanks
Alan
0
caandal
Asked:
caandal
  • 2
1 Solution
 
Mohit VijayCommented:
If you want to return a BigInt, based of DateTime value, that will be always unique

use

CAST(Convert(VARCHAR(50), GETDATE(), 112) + REPLACE(Convert(VARCHAR(50), GETDATE(), 114),':','') AS BIGINT)

To Test it write

SELECT CAST(Convert(VARCHAR(50), GETDATE(), 112) + REPLACE(Convert(VARCHAR(50), GETDATE(), 114),':','') AS BIGINT)

0
 
cyberkiwiCommented:
How about seconds from date 0 (1900-01-01) ?

convert(bigint, convert(float, datetimesubmitted)*24*60*60)
0
 
cyberkiwiCommented:
Or the Unix timestamp, which is number of seconds from 1970-01-01 12:00am

convert(int, (convert(float, datetimesubmitted)-convert(float, convert(datetime,'19700101')))*24*60*60)
0
 
caandalAuthor Commented:
Hi VjSoft
Thanks - I was one bracket away from the right solution and then chucked it out
Alan
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now