Creating SQL View, replaceing variables with actual variables doesn't work for me

Question

First, let me thank you for helping!

Now I'm converting a query to a view in SQL so I need to replace all my variables with actual values. However I am running into trouble when doing this.

When I have

    DECLARE @TweleveAM datetime
    set @TweleveAM = '1900-01-01 00:00:00'
    DECLARE @TweleveThirtyAM datetime
    set @TweleveThirtyAM = '1900-01-01 00:30:00'

    WHEN (cast(segstart as float) - floor(cast(segstart as float))) >= 
(cast(@TweleveAM as float) - floor(cast(@TweleveAM as float))) 
and (cast(segstart as float) - floor(cast(segstart as float))) < 
(cast(@TweleveThirtyAM as float) - floor(cast(@TweleveThirtyAM as float)))
THEN CAST('0' as int)

And instead I use

    WHEN (cast(segstart as float) - floor(cast(segstart as float))) >= 
(cast(cast('1900-01-01 00:00:00' as datetime)as float) - 
floor(cast(cast('1900-01-01 00:00:00' as datetime) as float))) 
and (cast(segstart as float) - floor(cast(segstart as float))) < 
(cast(cast('1900-01-01 00:00:30' as datetime) as float) - 
floor(cast(cast('1900-01-01 00:00:30' as datetime) as float)))
THEN CAST('0' as int)


ELSE Null End as Interval

My query produces Null, when in fact the data is never NULL.

What am I doing wrong here?

Answer 1

It looks like you're using SQLServer. Instead of casting your datetimes into floats to calculate time differences, I suggest using SQLServer's datediff function with the minute or second argument, as appropriate.

Answer 2

I agree with Mark that it looks like this can be simplified. To answer your question though. The two versions are not the same. The top one has

 DECLARE @TweleveThirtyAM datetime
    set @TweleveThirtyAM = '1900-01-01 00:30:00'

The second one has 30 seconds past midnight

'1900-01-01 00:00:30'

Though you should use ISO format 1900-01-01T00:30:00.000 anyway. With the correct value substituted in your query looks like

SELECT
  CASE
    WHEN
      (
        CAST(segstart AS FLOAT) - floor(CAST(segstart AS FLOAT))
      )
      >= (CAST(CAST('1900-01-01T00:00:00.000' AS DATETIME) AS FLOAT) - floor(CAST(CAST('1900-01-01T00:00:00.000' AS DATETIME) AS FLOAT)))
    AND
      (
        CAST(segstart AS FLOAT) - floor(CAST(segstart AS FLOAT))
      )
      < (CAST(CAST('1900-01-01T00:30:00.000' AS DATETIME) AS FLOAT) - floor(CAST(CAST('1900-01-01T00:30:00.000' AS DATETIME) AS FLOAT)))
    THEN CAST('0' as int)
    ELSE NULL
  END AS Interval
FROM T

This can be simplified to

SELECT
  CASE
    WHEN
      (
        CAST(segstart AS FLOAT) - floor(CAST(segstart AS FLOAT))
      )
      >= 0
    AND
      (
        CAST(segstart AS FLOAT) - floor(CAST(segstart AS FLOAT))
      )
      < 1.0/48
    THEN 0
    ELSE NULL
  END AS Interval
FROM T

I don't think the first part can ever be false so that can be removed as well leaving

SELECT
  CASE
    WHEN
        CAST(segstart AS FLOAT) - floor(CAST(segstart AS FLOAT)) < 1.0/48
    THEN 0
    ELSE NULL
  END AS Interval
FROM T

Which might be clearer as

SELECT
  CASE
    WHEN
        DATEPART(HOUR, segstart) = 0 AND DATEPART(MINUTE, segstart  < 30)
    THEN 0
    ELSE NULL
  END AS Interval
FROM T

Also (and somewhat more speculatively) unless that half hour has particular significance for your application I wonder if this is part of a much bigger case statement that divides the day up into 48 half hour intervals? If so this might do the job.

SELECT 2 * DATEPART(HOUR, segstart) +  DATEPART(MINUTE, segstart) / 30 AS Interval