Re: Data Type conversion from int to datetime

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 11/03/04 

Date: Tue, 2 Nov 2004 23:00:08 -0600

> CAST(tblSiteVisit.intDateStart AS DATETIME)

This conversion assumes the integer contains the number of days since
'1900-01-01'. If your integer contains a date value such as 20041102,
you'll need to first convert it to character and then to datetime. For
example:

    SELECT CAST(CAST(tblSiteVisit.intDateStart AS char(8)) as datetime) 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Guest" <Guest@aew_nospam.com> wrote in message 
news:OXJVMzUwEHA.2876@TK2MSFTNGP12.phx.gbl...
i have a datetime field that i want to calculate to equal those values in 
another field of dates as type integer. I had read somewhere that this would 
be an implicit conversion but it won't go. I've tried both CAST and nested 
CAST queries using various intermediate datatypes to get the conversion, but 
same results.
Suggestions greatly appreciated.
UPDATE tblMaterial
SET tblMaterial.dtIDDate = CAST(tblSiteVisit.intDateStart AS DATETIME)
FROM tblSiteVisit
INNER JOIN tblMaterial
ON tblSiteVisit.intSiteVisitID = tblMaterial.intSiteVisitID
WHERE tblMaterial.dtIDDate IS NULL
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
User submitted from AEWNET (http://www.aewnet.com/)

Relevant Pages

  • Re: Rights and Datetime conversion.
    ... This is why I suggested to you to do the conversion in the SP where you can ... Sylvain Lafontaine, ing. ... transmit the datetime as a varchar and do the conversion ... The report has the following InputParameters:- ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SQL bug, ISNULL function with smalldatetime
    ... didn't even think about the rounding and re-implicit conversion to ... >>For @dateParam, ... >>DECLARE @dateParam datetime ... > smalldatetime, rounded to the nearest full minute: 2005-02-10T00:54. ...
    (microsoft.public.sqlserver.programming)
  • Re: Time Conversion
    ... You could do the conversion manually. ... TimeSpan span = TimeSpan.FromSeconds; ... DateTime dateTime = base + span; ... which is Time in seconds since UTC 1/1/70 in theory. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Formatting DateTime with an offset
    ... DateTime a is sealed value type so you can not extend it. ... would like my users to be able to specify their prefered TimeZone. ... I have written the code to do the timezone conversion. ... rather have DateTime.ToString go through some kind of formatting that ...
    (microsoft.public.dotnet.framework.aspnet)
  • Formatting DateTime with an offset
    ... I have an application that stores all DateTime inforamtion in UTC. ... would like my users to be able to specify their prefered TimeZone. ... I have written the code to do the timezone conversion. ... rather have DateTime.ToString go through some kind of formatting that ...
    (microsoft.public.dotnet.framework.aspnet)