In my current project I need to query an MS SQL Server database. 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()) a
It's maybe my Oracle background, but I would write this like:
(SELECT GETDATE() dt) a
An alternative is:
select CURRENT_TIMESTAMP
I 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 ) utl
The 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.