Re: UTC dates in SQL 2000
From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 07/04/04
- Next message: John Peterson: "DTS: How to automatically "map" the transformations in Transform Data Task?"
- Previous message: Andy Ball: "Re: How to send email from DTS to a Lotus Notes Email user"
- In reply to: Andrew Robinson: "Re: UTC dates in SQL 2000"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 4 Jul 2004 08:33:13 +0100
Then have a look at this
select dateadd(day,-38169, '20040702')
It looks as though what you have is days after midnight on 31/12/1899
VBScript has an equivalent function for this.
-- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Andrew Robinson" <chunkydrew33136@hotmail.com> wrote in message news:257d901c4604c$148862c0$a301280a@phx.gbl... > 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. Are we talking about the same thing, or is Cisco > using UTC in a different way? > > > >-----Original Message----- > >There is no built in transform type of UTC --> Datetime > so you will have to > >roll your own so > > > >This is UTC format YYMMDDHHMMZ > > > >I would parse this in a Active Script transform > extracting the relevant > >parts and rebuilding it into a suitable string for a > datatime field in SQL > >Server. > > > >We do this partially here > > > >Formatting Character Data into Datetime fields > >(http://www.sqldts.com/default.aspx?249) > > > >-- > > > >Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > >www.SQLDTS.com - The site for all your DTS needs. > >I support PASS - the definitive, global community > >for SQL Server professionals - http://www.sqlpass.org > >"Andrew Robinson" <Andrew > Robinson@discussions.microsoft.com> wrote in > >message news:AFE6DC10-51CA-41D8-A499- > 610281D865A5@microsoft.com... > >> 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 > >is function I can use to convert these to datetime > format as part of the > >transformation? > > > > > >. > >
- Next message: John Peterson: "DTS: How to automatically "map" the transformations in Transform Data Task?"
- Previous message: Andy Ball: "Re: How to send email from DTS to a Lotus Notes Email user"
- In reply to: Andrew Robinson: "Re: UTC dates in SQL 2000"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
