sql-serverpostgresqlto-datedense-rank

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


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

Solution

  • 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