Unfortunately the dates are stored as a BigInt instead of a proper date datatype.
So I had to find out how to do compare the dates with the systemdate, and how to get the system date. To log this for possible later use, as an exception, a blog about SqlServer.
To get the system date, you can do:
(SELECT dt=GETDATE()) aIt's maybe my Oracle background, but I would write this like:
(SELECT GETDATE() dt) aAn alternative is:
select CURRENT_TIMESTAMPI found this at this blog. Contrary to the writer of that blog I would prefer this version, since I found that it works on Oracle too. There are several ways to convert this to a bigint, but the most compact I found is:
( SELECT YEAR(DT)*10000+MONTH(dt)*100+DAY(dt) sysdateInt FROM -- Test Data (SELECT GETDATE() dt) a ) utlThe way I wrote this, makes it usefull as a subquery or a joined query:
SELECT Ent.* , CASE WHEN Ent.endDate IS NOT NULL AND Ent.endDate-1 < sysdateInt THEN Ent.endDate-1 ELSE sysdateInt END refEndDateEntity , utl.sysdateInt FROM SomeEntity Ent, ( SELECT YEAR(DT)*10000+MONTH(dt)*100+DAY(dt) sysdateInt FROM -- Test Data (SELECT GETDATE() dt) a ) utl;To convert a bigint to a date, you can do the following:
CONVERT(DATETIME, CONVERT(CHAR(8), ent.endDate))However, I found that although this works in a select clause, in the where-clause this would run into a "Data Truncation" error. Maybe it is due to the use of SqlDeveloper and thus a JDBC connection to SqlServer, but I'm not so enthousiastic about the error-responses of SqlServer... I assume the error has to do with the fact that it has to do with the fact that SqlServer has to interpret a column-value of a row when it did not already selected it, that is when evaluating wheter to add the row (or not) to the result set. So to make it work I added the construction as a determination value in the select clause of a 1:1 view on the table, and use that view in stead of the table. Then the selected value can be used in the where clause.