Convert a numeric value to a valid date time

• ramsatish

  Old Hand

  Points: 380

  More actions

  August 18, 2008 at 12:19 pm

  #191445

  Hi All,

  Primary Task :- To import .txt file to sql server 2005.

  Challenge :-There is a field(Closed_date) in the .txt file which contains the value like "1205894254" which is equal to "19-Mar-08".

  Historic approach to know the value of closed_date:- We used to export that .txt file to excel.

  We used to apply a formula on closed_date field which is equal to

  25569 + value/86400 .

  For eg see the below table to see the equivalent date of closed_date column.

  close_date Date

  1205894254 19-Mar-08

  1205895171 19-Mar-08

  1205808001 18-Mar-08

  1205894933 19-Mar-08

  1205980687 20-Mar-08

  1205722185 17-Mar-08

  1205724847 17-Mar-08

  Need Solution for:-How to convert "closed_date" to a valid date format.

  Any help to this ould be really appreciated.

  Thanks in advance and have a Great Day !!

  Regards,

  Sriram Satish

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 18, 2008 at 1:11 pm

  #859620

  Looks like the data is the number of seconds since 1/1/1970 so using the DateAdd function should work. Look up DateAdd on BOL for details.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  August 18, 2008 at 1:28 pm

  #859631

  The F_UNIX_TIME_TO_DATETIME function on the link below will do what you want.

  UNIX Time Conversion Functions

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66858

  The code is simple, so you can also do it this way:

  select

  dateadd(ss,a.TIME_IN_SECONDS,25567)

  from

  MyTable a

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  August 18, 2008 at 2:07 pm

  #859662

  This will also work with you current formula, just by adjusting it by 2 days:

  Select cast(25567 + 1205894254/86400 as datetime)

  union all Select cast(25567 + 1205895171/86400 as datetime)

  union all Select cast(25567 + 1205808001/86400 as datetime)

  union all Select cast(25567 + 1205894933/86400 as datetime)

  union all Select cast(25567 + 1205980687/86400 as datetime)

  union all Select cast(25567 + 1205722185/86400 as datetime)

  union all Select cast(25567 + 1205724847/86400 as datetime)

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]