Re: Number to Date.

anonymous_at_discussions.microsoft.com
Date: 04/06/04 

Date: Tue, 6 Apr 2004 14:31:36 -0700

Dear Vishal,

Thank you for your help. Not being a whiz at building
queries for DBs it took me a long time to build the
original query for Oracle. Trying to find the
corresponding statements for MS SQL was taking me a very
long time. You have helped me plenty.

Thanks again,

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