Re: UTC dates in SQL 2000

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 07/04/04 

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?
> >
> >
> >.
> >

Relevant Pages

  • Re: Date Formats
    ... tables of SQL Server. ... I had problem in exporting these fields into Oracle. ... > I presume that the problem is that Oracle doesn't interpret the datetime values from SQL server ... > a format, hence trying to enforce a format for that datatype is meaningless. ...
    (microsoft.public.sqlserver.programming)
  • Re: convert(datetime, datefld, 101) error
    ... datefld is a varchar field in a sql server 2000 table, ... >already in a SQL Server table? ... >format that is completely unambiguous. ... stored as datetime, ...
    (microsoft.public.sqlserver.programming)
  • Re: Real to datetime - how to...?
    ... would like to have it in hh:mm:ss format. ... The division with 24 is necessary, because a datetime value consists ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Oh... no....
    ... select castas datetime) ... Obviously it would be better to use the "standard" format. ... Pro SQL Server 2000 Database Design ... >> data type of the date_variable in stored proc have been set to char ...
    (microsoft.public.sqlserver.programming)
  • Re: UTC dates in SQL 2000
    ... identifies as UTC, in the format 99999. ... getutcdatefunction to return the datetime for the ... Are we talking about the same thing, or is Cisco ...
    (microsoft.public.sqlserver.dts)