Neal Neal - 1 year ago 103
SQL Question

Cast(GetDate() as int) comes out incorrect after noon

So is this supposed to happen? or is something wrong with my query.
if I run the following query

select getdate() as [Date],
CAST(getdate() as date) as [Time],
CAST(getdate() as INT) as INT,
Cast(CAST(cast(getdate() as int) as DATETime) as Date) as finish


If I run this at 11:55:00 AM [finish] returns the correct date.

If I run the query at 12:10:00 PM [finish] returns tomorrows date.

I tried to search for this and couldn't find anything so sorry if its been noted before.

I'm trying to group by Date but I don't want to group by time also, that is why I am doing the conversions.

Running Microsoft SQL Server 2008 R2

Answer Source

I think your problem is that (if you absolutely have to do these converts, I know it used to be required long ago) that you are using int instead of float and getting rounding on your converted values:

select getdate() as [Date]
    ,CAST(getdate() as date) as [Time]
    ,CAST(getdate() as INT) as INT
    ,Cast(CAST(cast(getdate() as int) as DATETime) as Date) as finishInt
    ,CAST(getdate() as float) as FLOAT
    ,Cast(CAST(cast(getdate() as float) as DATETime) as Date) as finishFloat

Output:

+-------------------------+------------+-------+------------+------------------+-------------+
|          Date           |    Time    |  INT  | finishInt  |      FLOAT       | finishFloat |
+-------------------------+------------+-------+------------+------------------+-------------+
| 2017-02-07 16:47:59.823 | 2017-02-07 | 42772 | 2017-02-08 | 42771.6999979552 | 2017-02-07  |
+-------------------------+------------+-------+------------+------------------+-------------+
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download