Link to home
Get AccessLog in
Avatar of marrowyung
marrowyung

asked on

query to MS SQL

Dear all,

right now I tried to run the query from Oracle and this orace talk to MSSQL:

select "BeginDate",t.CRCYID, t."CurrencyAbbrv", "ExchangeRate" from
        (
        select a."CurrencyID" "CRCYID", "CurrencyAbbrv", max (b."BeginDate") "CRCYDATE"
         from
            dbo.tbCurrency@LINK_TELARIX_IXCORE_MAIN.GATEWAYCOMMS.COM a,
            dbo.tbExchange@LINK_TELARIX_IXCORE_MAIN.GATEWAYCOMMS.COM b
        where
            a."CurrencyID"=b."CurrencyID" and b."BeginDate" <= to_date(vDay,'YYYYMMDD')
        group by
            a."CurrencyID","CurrencyAbbrv"    
        ) t, dbo.tbExchange@LINK_TELARIX_IXCORE_MAIN.GATEWAYCOMMS.COM u
        where t.crcyid=u."CurrencyID" and t.crcydate=u."BeginDate" );


Question now is :

1) to_date(vDay,'YYYYMMDD') is not understand by the MS SQL, how can I replace it using it by today’s date in the MSSQL notation ?
SOLUTION
Avatar of Louis01
Louis01
Flag of South Africa image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of PortletPaul
and b."BeginDate" <= cast(vDay as date)


or just

and b."BeginDate" <= vDay /* this is an implicit type conversion performed for you */

I prefer the explict  cast(vDay as date)
I am presuming vDay is not the same as sysdate

getdate() and sysdate are equivalents
Avatar of marrowyung
marrowyung

ASKER

so just replace with getdate() , that's it?
You have not told us what the required date logic is, so I for one would be dubious about being definitive. If you want "today, right now (including the time)" then getdate() is your animal.

read on please



IF, in Oracle you would use sysdate, then in mssql you substitute getdate() e.g.
select
*
from sometable
where datefield >= (select sysdate from dual)

]select
*
from sometable
where datefield >= getdate()


HOWEVER....

for each and every date operation in mssql there is at least 20 options, for example

IF, you want to have today's date at time 00:00:00 THEN

select
*
from sometable
where datefield >= cast(getdate() as date) /* 20 options at least for this */

so,
IF

to_date(vDay,'YYYYMMDD') = sysdate (at time 00:00:00)

then


  where
            a."CurrencyID"=b."CurrencyID" and b."BeginDate" <= cast(getdate() as date)



IF vDay is NOT "today's date at 00:00:00", but you do want the equivalent of to_date(vDay, 'YYYYMMDD') and vDay is a varchar variable THEN

  where
            a."CurrencyID"=b."CurrencyID" and b."BeginDate" <= cast(vDay as date)




what data type is vDay? did you declare it as varchar, date, datetime, smalldatetime, time


If this comment only confuses you more, please describe what vDay is supposed to represent in your logic and I'll translate for you. But do please tell me what data type vDay is won't you.



----------------------------------------

You really will need to be familiar with the plethora of date function in TSQL
http://msdn.microsoft.com/en-us/library/ms186724.aspx

believe me, this is one of the more perplexing aspects of the transition from Oracle
PortletPaul,

I just get a query from Oracle to MS SQL and what I know is I need to convert it to current date or a simpel date only.
we just do it for a test on the speed of MS SQL query.

how about this:

 to_date ( to_char("CallDate",'DD/MM/YYYY'), 'DD/MM/YYYY'),

how to convert it to MS SQL?

select 
            to_date ( to_char("CallDate",'DD/MM/YYYY'), 'DD/MM/YYYY'),
            "AccountID",
            "Account",
            "CommercialTrunkID",
            "CommercialTrunk",
            decode ("Service",'ITFS',decode(upper("Direction"),'INBOUND','OUTBOUND','INBOUND'),upper("Direction")),
            "Country",
            "Destination",
            "SettlementDestination",
            "SettlementProduct",
            "Service",
            nvl ("Currency", '***'),
            b.currencyabbrv,
            "InRouteClass",
            "TariffType",
            b.exchangedate,
            b.exchangerate,
            sum("UnroundedCallDuration"), 
            sum("Minutes"), 
            decode ( instr(upper("TariffType"),'OPERATIONAL'), 0, sum("Amount"), 0),
            decode ( instr(upper("TariffType"),'ACCOUNTING'), 0, sum("Amount"), 0),
            decode ( instr(upper("TariffType"),'OPERATIONAL'), 0, sum("Amount")*b.EXCHANGERATE, 0),
            decode ( instr(upper("TariffType"),'ACCOUNTING'), 0, sum("Amount")*b.EXCHANGERATE, 0),
            Sum("Answered")
        from dbo.yvwRawCostRev_PCCW a, DEN_tlxXchangeRates b 
        where "CallDate"=to_date (vday,'YYYYMMDD') and a."CurrencyID"=b.crcyid
        group by
           to_date ( to_char("CallDate",'DD/MM/YYYY'), 'DD/MM/YYYY'),
            "AccountID",
            "Account",
            "CommercialTrunkID",
            "CommercialTrunk",
            decode ("Service",'ITFS',decode(upper("Direction"),'INBOUND','OUTBOUND','INBOUND'),upper("Direction")),
            "Country",
            "Destination",
            "SettlementDestination",
            "SettlementProduct",
            "Service",
            nvl ("Currency", '***'),
            b.currencyabbrv,
            "InRouteClass",
            "TariffType",
            b.exchangedate,
            b.exchangerate;

Open in new window

To get the current datetime in MS SQL Server:
SELECT GETDATE()

To get the current date in MS SQL Server (2008 +):
SELECT convert(date, GETDATE())

To convert a string date to datetime in MS SQL Server:
SELECT CONVERT ( datetime, '27/03/2013', 101 )
OR
SELECT CONVERT ( datetime, '20130327', 112 )

To convert a string date to date in MS SQL Server (2008+):
SELECT CONVERT ( date, '27/03/2013', 101 )
OR
SELECT CONVERT ( date, '20130327', 112 )

The 101 and 112 is a STYLE. See THIS for others

Note: Since SQL SERVER 2008 a date type has been introduced. Only datetime before that
this is OUTPUT as part of the selection list, and I believe it is a date (or datetime) field:

to_date ( to_char("CallDate",'DD/MM/YYYY'), 'DD/MM/YYYY')

=

convert(varchar, [CallDate] ,103)

see https://www.experts-exchange.com/blogs/PortletPaul/B_7503-SQL-Server-Date-Styles-formats-using-CONVERT.html
by the way, are you using sql server 2012? or earlier...

in 2012 there is a function very similar to to_date([value],[pattern])
it is FORMAT

do let me know because dealing with 'convert' for these date patterns is a bit tedious

good link for format (and comparsions to earlier)
http://www.sql-server-helper.com/sql-server-2012/format-function-vs-convert-function.aspx
ASKER CERTIFIED SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
PortletPaul,


We are using SQL server 2008 R2 with SP2 + CU4. is it making a different ? ok, you want to introduce the format command.


"Have you considered a tool to assist in your conversion?"

What tools it is, please give example ?
the format command is only available in sql server 2012, I mentioned it because it is like to_date (so its easier for Oracle folk to use). Sql Server date function are - well - just too many.

When I use the term 'tool' here I'm referring to 3rd party products, but I've no direct experience in conversion tools, but I do know they exist in the market e.g.

 (samples I've not used any so cannot vouch for any):

This may be of great interest:
Oracle To SQL Server Free Online Stored Procedure Migration Service helps you to convert Oracle PL/SQL Packages, Stored Procedures, Functions, Triggers, Tables, Views, Indexes, and other Oracle specific objects to SQL Server Transact-SQL.
http://search.swissql.com/oratosql/

Omega Sync
sqltran (from same company as Omega Sync)
Navicat
"Sql Server date function are - well - just too many."

I put a like on it !
you all good !