SQL Server Oracle to SQL NEXT_DAY (TRUNC (SYSDATE) query syntax

7lrncoxx 于 12个月前发布在 Oracle

关注(0)|答案(1)|浏览(217)

I'm working on a query that needs to be converted from Oracle to SQL for SSMS. I am stuck at this portion though:

WHERE
    (irrelevant to question portion of query)
    AND add_dt BETWEEN NEXT_DAY (TRUNC(SYSDATE) - 12, 'Monday')
                   AND NEXT_DAY (TRUNC(SYSDATE) - 6, 'Monday')

I understand what this does in Oracle, however, I am struggling with the conversion to T-SQL in SSMS.

I appreciate any help given!

I've tried to research converting NEXT_DAY to GETDATE and TRUNC to DATETIME but the SYSDATE is what is getting me.

sql-server

来源：https://stackoverflow.com/questions/77668273/oracle-to-sql-next-day-trunc-sysdate-query-syntax

1条答案

按热度按时间

9njqaruj1#

In Oracle, your query is identical to:

AND add_dt BETWEEN TRUNC(SYSDATE - 5, 'IW') AND TRUNC(SYSDATE + 1, 'IW')

Which can be converted to SQL Server as:

AND add_dt BETWEEN DATEADD(week, DATEDIFF(week, 0, DATEADD(day, -6, GETDATE())), 0)
               AND DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)

Or, from SQL Server 22:

AND add_dt BETWEEN DATETRUNC(iso_week, DATEADD(day, -5, GETDATE()))
               AND DATETRUNC(iso_week, DATEADD(day, +1, GETDATE()))

However, I'm not sure that you want to have the start and end dates of the range 6 days apart because you will get data from an 8-day period most days of the week but on Saturdays you only get a single day.

If you ware expecting to always get a range of 7 days then what you probably want is, in Oracle:

AND add_dt >= TRUNC(SYSDATE - 5, 'IW')
AND add_dt <  TRUNC(SYSDATE + 2, 'IW')

Or SQL Server:

AND add_dt >= DATEADD(week, DATEDIFF(week, 0, DATEADD(day, -6, GETDATE())), 0)
AND add_dt <  DATEADD(week, DATEDIFF(week, 0, DATEADD(day, 1, GETDATE())), 0)

Or, SQL Server 2022:

AND add_dt >= DATETRUNC(iso_week, DATEADD(day, -5, GETDATE()))
AND add_dt <  DATETRUNC(iso_week, DATEADD(day, +2, GETDATE()))

Oracle fiddle

SQL Server fiddle

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Oracle to SQL NEXT_DAY (TRUNC (SYSDATE) query syntax

1条答案

相关问题

热门标签

最新问答