Convert datetime to bigint

Question

Post reply

Convert datetime to bigint

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

gotqn SSC Veteran Points: 277 More actions · Answer 1

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 · Answer 2

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: 101098 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

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.