-1

I have this part of a query made in POSTGRE and I want to change it for using it in SQL Server, but there is an error in DATE() function.

AND DATE(A.SERVERTIME) = DATE(B.SERVERTIME) + 1

SERVERTIME is TIMESTAMP datatype.

I want to know how to get the date part from SERVERTIME.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Papillon
  • 1
  • 2
  • Are you using SQL Server or PostgreSQL? They are *completely* different products... Please tag correctly. I have removed references to both RDBMS because it's completely unclear which you are talking about. – Thom A Sep 30 '21 at 12:02
  • Sorry, you are right. I just changed it. I made it in PostgreSQL but now I need this in SQL Server. – Papillon Sep 30 '21 at 12:09
  • 2
    `timestamp` can't be converted to a date (and time) value in SQL Server. `timestamp` is a deprecated synonym for `rowversion`, it's not a date or time at all; it's a `binary(8)`. – Thom A Sep 30 '21 at 12:10
  • Does this answer your question? [How to convert SQL Server's timestamp column to datetime format](https://stackoverflow.com/questions/8119386/how-to-convert-sql-servers-timestamp-column-to-datetime-format) TL;DR: You can't. – Thom A Sep 30 '21 at 12:12
  • I actually found this solution: https://learn.microsoft.com/en-us/sql/t-sql/functions/current-timestamp-transact-sql?view=sql-server-ver15 – Papillon Sep 30 '21 at 12:19
  • `CURRENT_TIMESTAMP` returns a `datetime`, @Papillion, not a `timestamp`. That is **very clearly** stated in the documentation you inked, under the [**Return Type**](https://learn.microsoft.com/en-us/sql/t-sql/functions/current-timestamp-transact-sql?view=sql-server-ver15#return-type) section. – Thom A Sep 30 '21 at 12:21
  • Does this answer your question? [How to return only the Date from a SQL Server DateTime datatype](https://stackoverflow.com/questions/113045/how-to-return-only-the-date-from-a-sql-server-datetime-datatype) – Thom A Sep 30 '21 at 12:21

1 Answers1

0

Assuming you want to add days;

AND cast(A.SERVERTIME as date)= cast(DATEADD(day, 1, B.SERVERTIME) as date)

Edit: As Larnu warned, timestamp can not be casted to date. However it can be casted to date if it goes through DATEADD function.

Hence it should be;

AND cast(DATEADD(day, 0, A.SERVERTIME) as date)= cast(DATEADD(day, 1, B.SERVERTIME) as date)
dkc
  • 21
  • 3
  • *IF* this works, then: 1. The OP's question is wrong, the column is not a `timestamp`. 2: There are *plenty* of existing questions on how to convert a date and time value to a date (including a [canonical duplicate](https://stackoverflow.com/questions/113045/how-to-return-only-the-date-from-a-sql-server-datetime-datatype). 3: As such the question should be closed as a dupe. – Thom A Sep 30 '21 at 12:19
  • I checked in my environment. create table testdate (id integer, ts timestamp); insert into testdate values (1,DEFAULT); select *, dateadd(day, 1, ts), cast(dateadd(day, 1, ts) as date) from testdate; It gives; id ts (No column name) (No column name) 1 0x00000000000007D2 1900-01-02 00:00:06.673 1900-01-02 -- timestamp cant be converted however it can be converted if dateadd is used. However you are right, there is some error. left hand should also be worked with dateadd functions. I will edit this thank you. – dkc Sep 30 '21 at 12:28
  • The date values from get from the `timestamp` are nonsense values though, @dkc. [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4766bbc75253d385024ebc743de84f42). *Again*, `timestamp` is **not** a date and time value in SQL Server. – Thom A Sep 30 '21 at 12:45