convert date format of oracle query to sql server query

Question

i am trying to convert below oracle query to SQL server query.

Select * from table A WHERE TRUNC(a.Generate_DATE,'MM') = (SELECT (NVL (TRUNC (TO_DATE ('$$date','MM/DD/YYYY HH24:MI:SS')),TRUNC(ADD_MONTHS(SYSDATE, -1),'MM'))) FROM DUAL)

where $$date is parameter value and will be passed from outside the query.

The issue is with dateformat.I am unable to convert this exact format in sql server.

If you're using SQL Server, use a well typed parameter in the first place, and you don't need any of these conversion processes, just basic date logic. — Thom A
– Thom A ♦, Commented Feb 16, 2021 at 15:13
I can't read PL\SQL so I can't translate the above for you, but by basic, I literally mean basic, like WHERE DateColumn = @DataParameter or perhaps WHERE DateColumn >= @StartDate AND DateColumn < @EndDate. — Thom A
– Thom A ♦, Commented Feb 16, 2021 at 15:18
its not simple date filters applied.We are doing some transformation on date like trunc(date,'MM') in oracle this gives first date of the month.Second part is using NVL function.Looking for the equivalent function in sql server.But whatever i saw in google there is no direct function available — gunjan gupta
– gunjan gupta, Commented Feb 16, 2021 at 15:23
"We are doing some transformation on date like trunc(date,'MM') in oracle this gives first date of the month." Don't do this, it's not SARGable. Instead of changing your column's date to be the first date of the month use date boundaries; like I showed in my above comment. Instead of changing the column's value to 2021-02-01 look for rows where the column's value is >= '2021-02-01' and < '2021-03-01'. Like I said, basic date logic. — Thom A
– Thom A ♦, Commented Feb 16, 2021 at 15:27

David Browne - Microsoft · Accepted Answer · 2021-02-16 15:33:58Z

To get the first day of the month use dateadd and day, eg

declare @d date = getdate() select dateadd(day, 1-day(@d), @d)

For NVL use the standard coalesce function.

For TRUNC use cast(getdate() as date)

For ADD_MONTHS(SYSDATE, -1) use dateadd(month,-1,getdate())

or go get the SQL Server Migration Assistant for Oracle, which will translate Oracle SQL and PL/SQL to TSQL for you. It also installs a bunch of compatibility functions so with SSMA this is translated to:

WHERE ssma_oracle.trunc_date2(A.END_DATE, 'MM') = ( SELECT isnull(ssma_oracle.trunc_date(ssma_oracle.to_date2('12/01/2020 18:15:00', 'MM/DD/YYYY HH24:MI:SS')), ssma_oracle.trunc_date2(dateadd(m, -1, sysdatetime()), 'MM')) AS expr )

Collectives™ on Stack Overflow

convert date format of oracle query to sql server query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related