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

Question

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

The equivalent of `DENSE_RANK` is `DENSE_RANK`. As for `TO_DATE` it isn't needed in SQL Server. If you're using a literal string, just provide an unambiguous string literal (either `yyyyMMdd` or `yyyy-MM-ddThh:mm:ss.nnnnnnn`). — Thom A, Sep 24 '21 at 11:43
TIME_COLUMN format is DATETIME and time_parameter comes always in the specified format — yed2393, Sep 24 '21 at 11:52
@yed2393 you don't need `TO_DATE` in PostgreSQL if you use date-typed fields and parameters. `DATETIME` has NO FORMAT IN EITHER DATABASE. It's a binary value. All databases except SQLite have built-in date types. Formats apply only when you display dates as strings or when you have to parse strings into dates. — Panagiotis Kanavos, Sep 24 '21 at 12:02
@AaronBertrand the code is converting a date variable into a string then parses it back into a date — Panagiotis Kanavos, Sep 24 '21 at 12:04
@AaronBertrand % is used in string interpolation in PostgreSQL, but not `%abc%` and not by itself. Perhaps this is just a script and variables are replaced using plain old string replacement? In that case the fix would be to declare the variables at the top and use better types and/or formats, et `declare @time_parameter datetime2(0)='%time_parameter%` — Panagiotis Kanavos, Sep 24 '21 at 12:15
@AaronBertrand `What happens if you SET LANGUAGE French;` that's why `datetime2(0)` would be better. Or the unseparated date form. But always *outside* the queries themselves — Panagiotis Kanavos, Sep 24 '21 at 12:16
@AaronBertrand this isn't a wholesale type change request. This is an attempt to change a script from one dialect to another. This means a lot is going to change to begin with, so it's an opportunity to clean up the script itself. The PostgreSQL script would benefit from declaring proper variables — Panagiotis Kanavos, Sep 24 '21 at 12:19
@AaronBertrand so any comments on the disappearance of SQL Server 2021? I say aliens. On this problem, since it's a conversion, there's no better time to use proper script variables. On the other hand, if there are 65536 lines of such script and people hope they can just write a regex to replace `TO_DATE` with `CONVERT`, they may resists. Even if a similar regex could replace `TO_DATE('%name%')` with `@name` — Panagiotis Kanavos, Sep 24 '21 at 12:33

Aaron Bertrand · Answer 1 · 2021-12-28T04:11:12.713

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.

score -2 · Accepted Answer · answered Sep 25 '21 at 10:03

-2

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

answered Sep 25 '21 at 10:03

yed2393

262
1
12

This is exactly what my answer from 2 days ago said, except I added a style number for safety. Your code will still break (either with an error or, worse, silently store invalid data without telling you a thing about it) if, say, [someone uses a different language](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e3d6a42bdb7d317cbeaa492707fecf24). Maybe different languages aren't a concern for you, but at this site we also care about future readers, who might be fooled into believing your code is safe for them, too, when it definitely is not. – Aaron Bertrand Sep 26 '21 at 22:46

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

2 Answers2