how to convert int to datetime
in [Using MS SQL Server]
|
From: hayko98 on 6 Oct 2009 14:43 I have databese where LDATE column uses int datatype to stored dates(i dont know why db designer used int to store datetime).how can i convert int to datetime? Thank you
From: Plamen Ratchev on 6 Oct 2009 16:22 Can you provide examples of INT values and how they map to corresponding DATETIME values? A guess is that you can use the DATEADD function to add specific period (days, minutes, etc.) depending on what that INT number represents. A good start is that the based date/time value 1900-01-01 is equivalent to 0 (that is if you cast 0 to DATETIME it results in 1900-01-01). If that is how dates are stored then you can simply do: DATEADD(DAY, n, '19000101') In the above 'n' is the INT value. -- Plamen Ratchev http://www.SQLStudio.com
From: vardan hakopian on 6 Oct 2009 17:22 On Oct 6, 1:22 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote: > Can you provide examples of INT values and how they map to corresponding DATETIME values? A guess is that you can use > the DATEADD function to add specific period (days, minutes, etc.) depending on what that INT number represents. > > A good start is that the based date/time value 1900-01-01 is equivalent to 0 (that is if you cast 0 to DATETIME it > results in 1900-01-01). If that is how dates are stored then you can simply do: > > DATEADD(DAY, n, '19000101') > > In the above 'n' is the INT value. > > -- > Plamen Ratchevhttp://www.SQLStudio.com For example '20010101'.i would like to delete some data that older than 2005-12-31
From: Plamen Ratchev on 6 Oct 2009 17:26 If this is YYYYMMDD, then it is very simple, just cast to character format and then to date: SELECT CAST(CAST(20010101 AS CHAR(8)) AS DATETIME) You can delete like: DELETE FROM Foo WHERE date < '20051231'; -- Plamen Ratchev http://www.SQLStudio.com
From: hayko98 on 6 Oct 2009 17:42
On Oct 6, 2:26 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote: > If this is YYYYMMDD, then it is very simple, just cast to character format and then to date: > > SELECT CAST(CAST(20010101 AS CHAR(8)) AS DATETIME) > > You can delete like: > > DELETE FROM Foo > WHERE date < '20051231'; > > -- > Plamen Ratchevhttp://www.SQLStudio.com When i am trying to run SELECT CAST(CAST(LDate AS char(8)) AS DATETIME) from dbo.Schedules i am getting error massage Conversion failed when converting datetime from character string. |