Re: Number to Date.

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 04/05/04 

Date: Mon, 5 Apr 2004 23:53:41 +0530

hi Dylan,

create table date_log (dt int)
insert into date_log values(20040405)

--you write a query to make conversion to datetime as follows.

select convert(datetime, cast(dt as char(8))) 'dt' from date_log

Make use of datetime datatype instead to avoid datetime value overflow errors and to insert
valid values of date datatype range. This is because datetime store dates in the range from
January 1, 1753 through December 31, 9999. However if you make use of INT datatype to store
datetime value,it can accept value of Dec 31, 1752 (which will be converted to integer as
17521231) though this valid integer its not a valid datetime value.

Ex:

insert into date_log values(17521231)
--Integer value can be inserted into above

select convert(datetime, cast(dt as char(8))) 'dt' from date_log

--in above query which tries to convert integer 17521231 to datetime, you will get an error as

The conversion of a char data type to a datetime data type resulted in an out-of-range
datetime value. 

-- 
Vishal Parkar
vgparkar@yahoo.co.in

Relevant Pages