Re: Number to Date.

From: Dylan (anonymous_at_discussions.microsoft.com)
Date: 04/06/04 

Date: Tue, 6 Apr 2004 03:54:18 -0700

Hello,

Thanks for you input Vishal. Below I have copied the
script I used to use for this when it was on an Oracle
Database. Do you know how I can make it work in MSSQL:

internal/oracle @sun
set pagesize 30
break on accnt_code skip page duplicates
spool c:\aged_debt_rep
select *
from (select accnt_code
, to_date(due_date,'YYYYMMDD') due_date, amount
from salfldgtel
WHERE ( ( ( ACCNT_CODE LIKE '51%')
AND ( ( ALLOCATION <> 'A' ) ) ) )
AND accnt_code <> '511534'
AND accnt_code <> '512088'
AND accnt_code <> '512165'
AND accnt_code <> '515124'
AND accnt_code <> '512199'
AND accnt_code <> '518020'
AND accnt_code <> '511538'
AND due_date between 19000101 and 20050101
and trunc(mod(due_date,1E4)*1E-2) between 1 and 12
and mod(due_date,1E2) between 1 and 31)x
where to_date(&p_custom_date,'DDMMYYYY') - x.due_date>60
order by accnt_code;
spool off
exit

Its the trunc(mod into 'x' variable that I need to
replicate in a MSSQL environment.

Thanks again.

Dylan

>-----Original Message-----
>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