convert datetime to integer

Question

convert datetime to integer

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

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

DBA-640728 SSChampion Points: 12898 More actions · Answer 1

thank you so much!!! one last question, i did this in my testing environment and worked perfectly; however in my production env i have replication, would this represent a problem to my subscriber?

Andrew Diniz SSCommitted Points: 1852 More actions · Answer 2

DBA-640728 (5/6/2009)
thank you so much!!! one last question, i did this in my testing environment and worked perfectly; however in my production env i have replication, would this represent a problem to my subscriber?

Careful! This doesn't hold true when GETDATE() returns a PM time. CONVERT(int, <datetime>) rounds to the nearest int - not truncates.

Try it out:

DECLARE @pm_date datetime;

SET @pm_date = '20120131 12:00:01';

SELECT

DATEDIFF(DAY, '19000101', @pm_date),

CAST(@pm_date AS int)

mt.bike.it SSC Journeyman Points: 75 More actions · Answer 3

I am in search of this as well... over 5000 records all in date format. It is indeed a date yyyymmdd.

Problem is that whoever created the table in the database that I need to import into has the type as a decimal 😉

So - I just want to change my column so I can import it and yet Excel is outsmarting me - wanting to give me the number equivalent. I have to keep the same "date" values.

I tried copying the values into a new column = general, special, number equivalent 🙂

Insanity to outsmart what it thinks I want lol - 20110219 turns into 40593

I understand why it converts - I need to outsmart Excel for my purposes.

john.arnott SSChampion Points: 11882 More actions · Answer 4

@mt.bike.it,

You'll want to use the "Paste Special" menu option in Excel to "paste values" rather than contents.