CODEWITHSUNDEEP
sqlsql-servert-sql
SSISPissesMeOff
SSISPissesMeOff

Reputation: 450

Working with timestamps in sql

I have an if statement in a stored procedure that is being used to derive some time value. It looks like this: 

    DECLARE @foo TIME
    IF (SELECT CONVERT(TIME,SYSUTCDATETIME())) > '15:00'
        SET @foo = DATEADD(hh,-15,CONVERT(TIME,SYSUTCDATETIME()))
    ELSE
        SET @foo = DATEADD(hh,+9,CONVERT(TIME,SYSUTCDATETIME()))

later on i would like to use that value in the following WHERE clause: 

AND created_at > DATEADD(hh,-@DailyLimitOffsetTime, CONVERT(TIME,SYSUTCDATETIME()))

I keep on getting an error that the data type time is invalid for the minus operator. How can i get around this to make the and clause work. I have tried converting the data type and i somewhat understand the issue DATEADD(hh) is looking for param 2 to be an int not a time. Is there some easier way to do this, I must admit if you couldn't tell already I am not good with timestamps at all. Any help is appreciated. 

DECLARE @DailyLimitOffsetTime TIME
IF (SELECT CONVERT(TIME,SYSUTCDATETIME())) > '15:00'
    SET @DailyLimitOffsetTime = DATEADD(hh,-15,CONVERT(TIME,SYSUTCDATETIME()))
ELSE
    SET @DailyLimitOffsetTime = DATEADD(hh,+9,CONVERT(TIME,SYSUTCDATETIME()))

IF @Limit <=
(
   SELECT COUNT(*)
   FROM dbo.fooTable
   WHERE offerId = @OfferID
   AND created_at > 
   DATEADD(hh,DATEPART(hh,-@DailyLimitOffsetTime),CONVERT(TIME,SYSUTCDATETIME()))
)
    SET @ErrorTypeID = 9400

I am sure there is a much better way to do this, and if so please share how. As always any help is appreciated. If you need any further explanation on the issue let me know. Its a bit messy this one.

Upvotes: 1

Views: 1468

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Second argument in DATEADD function has to be resolved to an int

AND created_at > DATEADD(hh, -DATEPART(hh, @DailyLimitOffsetTime), CONVERT(TIME,SYSUTCDATETIME()))

OR

DECLARE @time time = CONVERT(TIME, SYSUTCDATETIME())
IF @Limit <=
(
   SELECT COUNT(*)
   FROM dbo.fooTable
   WHERE offerId = @OfferID
   AND created_at >
   CASE WHEN @time > '15:00' 
        THEN DATEADD(hh, 15,  DATEADD(hh, -DATEPART(hh, @time), @time))
        ELSE DATEADD(hh, 09,  DATEADD(hh, -DATEPART(hh, @time), @time)) END 
   END    
)
SET @ErrorTypeID = 9400

Upvotes: 1

djangojazz
djangojazz

Reputation: 13242

I don't get what you are doing as you will always make the same time and not account for the date just the time. You don't want that if you are trying to show a different day forward when the UTC time is after 3 PM at a location. So if you want to go nine hours ahead you won't because you are only accounting for the current day by using the time frame. So if it was 8 PM of 1-16-12 you would set the time to be 5 AM. I think you need to set the DATETIME for what you want, not just the time.

DECLARE @foo Datetime
    IF (SELECT CONVERT(TIME,SYSUTCDATETIME())) > '15:00'
        SET @foo = Dateadd(hh, datediff(hh, 0, sysutcdatetime()) -15, 0)
    ELSE
        SET @foo = Dateadd(hh, datediff(hh, 0, sysutcdatetime()) + 9, 0)

select @foo

Upvotes: 0

Related Questions