Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • 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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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