question

blessenmk avatar image
blessenmk asked

Oracle SQL to SQL server date range

I am in the middle of converting Oracle SQL to MS SQL Query equivalent. How do I convert the below? :-


(SELECT distinct (to_char(SYSDATE - ROWNUM + 1, 'MM/DD/YY')) dtval

FROM DUAL

CONNECT BY ROWNUM < 90) dt


Would a CTE be required? Is there a simple straight forward conversion?

oraclemssqloracle-sql-developermssql2008
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Guessing here as don’t know Oracle but sounds like you want this

Convert(varchar(8), getdate() - rownum + 1, 1)

So convert to string

What you want to convert

Then 1 as the date format 1 = mm/dd/yy

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

blessenmk avatar image
blessenmk answered

I would need to change the syntax to row_number() over (order by <some column>) though. And it would need to include the 90 day condition too. I'm not sure how to incorporate all of this.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Post some sample data and expected outcome and it will be easier to see what your trying to do and come up with the query for it.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.