| Computers Forum Index » Computer - Databases - MS SQL Server » how to convert int to datetime... |
|
Page 1 of 1 |
|
| Author |
Message |
| hayko98... |
 Posted: Tue Oct 06, 2009 6:43 pm |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
| vardan hakopian... |
| Posted: Tue Oct 06, 2009 9:22 pm |
|
|
|
Guest
|
On Oct 6, 1:22 pm, Plamen Ratchev <Pla... at (no spam) SQLStudio.com> wrote:
Quote: 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 |
|
|
| Back to top |
|
|
|
| hayko98... |
| Posted: Tue Oct 06, 2009 9:42 pm |
|
|
|
Guest
|
On Oct 6, 2:26 pm, Plamen Ratchev <Pla... at (no spam) SQLStudio.com> wrote:
Quote: If this is YYYYMMDD, then it is very simple, just cast to character format and then to date:
SELECT CAST(CAST(20010101 AS CHAR(  ) 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() AS DATETIME) from dbo.Schedules
i am getting error massage
Conversion failed when converting datetime from character string. |
|
|
| Back to top |
|
|
|
| Plamen Ratchev... |
| Posted: Wed Oct 07, 2009 12:22 am |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
| Plamen Ratchev... |
| Posted: Wed Oct 07, 2009 1:26 am |
|
|
|
Guest
|
If this is YYYYMMDD, then it is very simple, just cast to character format and then to date:
SELECT CAST(CAST(20010101 AS CHAR() AS DATETIME)
You can delete like:
DELETE FROM Foo
WHERE date < '20051231';
--
Plamen Ratchev
http://www.SQLStudio.com |
|
|
| Back to top |
|
|
|
| Plamen Ratchev... |
| Posted: Wed Oct 07, 2009 3:36 am |
|
|
|
Guest
|
That means that some values are invalid dates. Try this:
SELECT CASE WHEN ISDATE(CAST(LDate AS CHAR()) = 1
THEN CAST(CAST(LDate AS CHAR() AS DATETIME)
END
FROM dbo.Schedules;
--
Plamen Ratchev
http://www.SQLStudio.com |
|
|
| Back to top |
|
|
|
| hayko98... |
| Posted: Thu Oct 08, 2009 6:17 pm |
|
|
|
Guest
|
On Oct 6, 4:36 pm, Plamen Ratchev <Pla... at (no spam) SQLStudio.com> wrote:
Quote: That means that some values are invalid dates. Try this:
SELECT CASE WHEN ISDATE(CAST(LDate AS CHAR( )) = 1
THEN CAST(CAST(LDate AS CHAR( ) AS DATETIME)
END
FROM dbo.Schedules;
--
Plamen Ratchevhttp://www.SQLStudio.com
Thank You very much.It worked. |
|
|
| Back to top |
|
|
|
| Madhivanan... |
| Posted: Tue Oct 20, 2009 1:08 pm |
|
|
|
Guest
|
On Oct 7, 4:36 am, Plamen Ratchev <Pla... at (no spam) SQLStudio.com> wrote:
Quote: That means that some values are invalid dates. Try this:
SELECT CASE WHEN ISDATE(CAST(LDate AS CHAR( )) = 1
THEN CAST(CAST(LDate AS CHAR( ) AS DATETIME)
END
FROM dbo.Schedules;
--
Plamen Ratchevhttp://www.SQLStudio.com
Note that isdate() is not reliable
You need to check the length too
SELECT CASE WHEN ISDATE(CAST(LDate AS CHAR()) = 1 and LEN(LDate)=8
THEN CAST(CAST(LDate AS CHAR() AS DATETIME)
END
FROM dbo.Schedules; |
|
|
| Back to top |
|
|
|
|
