Problem converting UTC to datetime in DTS

• A. A. Robinson

  Valued Member

  Points: 58

  More actions

  July 2, 2004 at 10:58 am

  #59273

  I need to create a DTS package to transfer data from SQL to a text file.

  My problem is, all the datetime fields in the db are in UTC format. Is there 

  a function I can use to convert these to datetime format as part of the

  transformation?  This db provides dates, which the developer (CISCO) identifies as UTC, in the 

  format 99999.

  Using this, 38169 converts to 7/2/04. I can use the getutcdate() function to return the datetime for the current UTC, or I can CONVERT(int, datetime) to convert a datetime into UTC, but neither will work in an 

  ActiveX script. 

  Also, is this truly UTC, or is Cisco using the term in a different sense?

• Linda Johanning

  SSCertifiable

  Points: 6727

  More actions

  July 6, 2004 at 11:57 am

  #513505

  Try one of these.  You may need to use other date styles though.

  SELECT RIGHT(DateAdd(s, 38169, getdate()),20)

  SELECT CONVERT(varchar(30),DateAdd(s, 38169, getdate()),109)

  We also use Cisco but the date/time field looks like 1089139787 (Greenwich Mean Time).  Hope this helps.

  Linda