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.

Answer 1

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
   )