Wednesday, 9 April 2014

SQLServer: date conversions

In my current project I need to query an MS SqlServer 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.

No comments: