Re: Number to Date.

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

  • Next message: kdabda: "Group By Clause taking too long." 
    Date: Wed, 7 Apr 2004 01:23:15 +0530

    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/MigrateOracle.asp 

    -- 
Vishal Parkar
vgparkar@yahoo.co.in
  • Next message: kdabda: "Group By Clause taking too long."

    Relevant Pages