Convert datetime to bigint

• gotqn

  SSC Veteran

  Points: 277

  More actions

  December 23, 2014 at 2:38 am

  #1766133

  I really want to see the performance difference between index on int/bigint column and datetime2 column.

• Wilfred van Dijk

  SSCrazy Eights

  Points: 9003

  More actions

  December 30, 2014 at 4:00 am

  #1766960

  my 50 cents:

  select cast(replace(replace(replace(replace(convert(varchar, current_timestamp,121),'-',''),':',''),' ',''),'.','') as bigint)

  Wilfred
  The best things in life are the simple things

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  December 30, 2014 at 9:10 am

  #1767020

  Charles Kincaid (12/22/2014)

  The only other reason that I can see for something like this is since the Datetime format uses a singed integer for the date portion that there might be some issues with dates before 1900 being viewed as higher than dates after that as the earlier dates would be negative. That might cause something if your dates are both sides of 1900. I guess that since that nobody relies on the sequence of data coming out of an index that all such issues are well hidden by ORDER BY.

  Dates before 19000101 are negative, dates after are positive. Thus, I believe they should all still sort correctly.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 11, 2015 at 1:10 pm

  #1797071

  hjelmesethe (12/22/2014)

  ---

  zerbit (12/22/2014)

  ---

  I'm really missing the scope of this conversion :doze: ..

  It sound likes this script is doing a large amount of CPU work for just change 8bytes into another 8byte representation.

  If i would need a conversion from DateTime to BigInt why not just use something like:

  select cast(cast(GETDATE() as timestamp) as bigint)

  I'm pretty sure that sql will do this job in a better way than calling a lot of "datepart".

  After all, i miss why the need of this conversion since a can actually put indexes on datetime columns too!

  What am i wrong with? :doze:

  The original poster's code creates a human-readable date/time in a bigint format. For example, if you run the poster's code you'll get a value like 20141222133915, which you can easily look at and determine it is 22/Dec/2014 13:39:15.

  The code you've provided indeed converts the datetime to a bigint, but I have no idea what date/time 180358576407463 represents 🙂

  Now that makes sense. Thanks.