I have the below query coming from the Postgres database. I want to convert the below query from Postgres to an Azure SQL Server version.
I know that TO_DATE can be written as convert(DATETIME,...) but I want to protect the date format too. Even after changing TO_DATE, there are still errors. Can someone help me with this?
SELECT b.*
FROM (
SELECT MAX(gs.ID),
dense_rank() over (order by gs.TIME_COLUMN DESC ) AS latest
FROM TEST_TABLE gs
WHERE TIME_COLUMN BETWEEN TO_DATE('%time_parameter%', 'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE('%time_parameter2%', 'YYYY-MM-DD HH24:MI:SS')
GROUP BY gs.OTHER_ID, gs.TIME_COLUMN
) a
LEFT JOIN TEST_TABLE b ON max.latest = b.ID
This is the SQL Server version of the above query. Thanks for the discussions but this one and a bit of trying solved the issue.
SELECT b.*
FROM (
SELECT MAX(gs.ID) as max,
dense_rank() over (order by gs.TIME_COLUMN DESC ) AS latest
FROM TEST_TABLE gs
WHERE TIME_COLUMN BETWEEN CONVERT(DATETIME, '%time_parameter%')
AND CONVERT(DATETIME, '%time_parameter2%')
GROUP BY gs.OTHER_ID, gs.TIME_COLUMN
) a
LEFT JOIN TEST_TABLE b ON a.max = b.ID