Re: Number to Date.

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

Date: Wed, 7 Apr 2004 05:00:43 -0700

Hi vishal,

declare @p_custom_date is supposed to be a prompt for
users to enter a date. Is this how you pull this off in
SQL? When I run it in Query Analyzer it doesn't prompt
me and so it returns no rows. I manually enter a date in
the place of @p_custom_date for the datediff clause it
seems to work.

Any ideads?

Thanks,

Dylan
 
>-----Original Message-----
>Try following:
>
>declare @p_custom_date datetime
>
>select *
>from (select accnt_code
>, convert(char(8),due_date) 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'
>--if due_Date is integer
>AND convert(datetime, cast(due_date as char(8)))
between '19000101' and '20050101'
>--and trunc(mod(due_date,1E4)*1E-2) between 1 and 12 CAN
BE CHANGED TO
>and round(((due_date % 10000) * .01),0,1) between 1 and
12
>-- and mod(due_date,1E2) between 1 and 31)x CAN BE
CHANGED TO
>and (due_date % 100 ) between 1 and 31)x
>--to_date(&p_custom_date,'DDMMYYYY') - x.due_date > 60
>--above clause can be changed as follows, assuming you
are looking for difference in two dates
>in terms of number of days.
>where datediff(dd,@p_custom_date , x.due_date) > 60
>order by accnt_code
>
>
>dylan,
>as you must have realized that,you can not straight away
put the query that is working in
>oracle into sql server. simply because the functions
like trunc/mod and other expressions like
>1E4/1E2 can not be accepted by sql server as it is. I've
tried giving you closest possible
>answer. Try referring to following url which will be
more helpful to you.
>
>Migrating Oracle Databases to SQL Server 2000
>
>http://microsoft.com/sql/techinfo/deployment/2000/Migrate
Oracle.asp
>
>
>--
>Vishal Parkar
>vgparkar@yahoo.co.in
>
>
>
>.
>

Relevant Pages