SQL Server equivalent of dense_Rank() and TO_DATE() of Postgres-CodePudding

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

CodePudding user response：

The equivalent to ensuring YYYY-MM-DD isn't incorrectly interpreted as YYYY-DD-MM in some languages is to explicitly specify a style number during the convert:

WHERE TIME_COLUMN 
      BETWEEN CONVERT(datetime, '%time_parameter%',  21)
          AND CONVERT(datetime, '%time_parameter2%', 21)

For a full list of styles, see Build a cheat sheet for SQL Server date and time formats.

As Larnu and Panagiotis commented, it would be much better if you use a language-neutral format, like yyyy-MM-ddThh:mm:ss.nnn, and/or datetime2 in place of datetime, which will prevent language settings from interfering with datetime parsing.

And as an aside, BETWEEN should generally be avoided for date range queries; see the first couple of links at Dating Responsibly.

CodePudding user response：

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