convert datetime to integer

• DBA-640728

  SSChampion

  Points: 12898

  More actions

  May 6, 2009 at 8:48 am

  #989603

  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

  March 8, 2012 at 4:47 am

  #1456452

  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

  March 28, 2012 at 1:53 pm

  #1465971

  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

  March 28, 2012 at 3:56 pm

  #1466064

  @mt.bike.it,

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