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 ?
Microsoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
SOLUTION
Louis01

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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)
PortletPaul

I am presuming vDay is not the same as sysdate

getdate() and sysdate are equivalents
marrowyung

ASKER
so just replace with getdate() , that's it?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PortletPaul

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
marrowyung

ASKER
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.
marrowyung

ASKER
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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Louis01

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
PortletPaul

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
PortletPaul

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
marrowyung

ASKER
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 ?
PortletPaul

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
marrowyung

ASKER
"Sql Server date function are - well - just too many."

I put a like on it !
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
you all good !