Results 1 to 13 of 13
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: My T-SQL script, what's wrong with it?

    I have this SPROC that is executed by a SSIS every hour. Basically, between 1AM-6AM, I don't want the SPROC to execute what ever is inside. However, it still keeps running whatever is inside the IF statement. Any ideas?

    TIA

    -------------------------------------------------------------------------------------
    TRUNCATE TABLE DataSubscription

    DECLARE @StartTime AS VARCHAR(100)
    DECLARE @EndTime AS VARCHAR(100)
    SET @StartTime = CONVERT(CHAR(10),GETDATE(),101) + ' 01:00AM'
    SET @EndTime = CONVERT(CHAR(10),GETDATE(),101) + ' 06:00AM'

    IF GETDATE() NOT BETWEEN CAST(@StartTime AS DateTime) AND CAST(@EndTime AS DateTime)
    BEGIN
    -- Do stuff here.
    END;

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Probably because you are doing everything NOT between the two times. If you just want to do something between those two times then drop the NOT.

    P.S. This is post 170 in eight years. I need to slow down on my postings or get burned out.

    ---
    Revised - need to read more closely next time...
    ---
    DECLARE @StartTime AS VARCHAR(100)
    DECLARE @EndTime AS VARCHAR(100)
    DECLARE @Getdate as datetime
    SET @Getdate = 'Mar 14 2012' + ' 04:00AM'
    SET @StartTime = CONVERT(CHAR(10),GETDATE(),101) + ' 01:00AM'
    SET @EndTime = CONVERT(CHAR(10),GETDATE(),101) + ' 06:00AM'

    IF @Getdate NOT BETWEEN CAST(@StartTime AS DateTime) AND CAST(@EndTime AS DateTime)
    BEGIN
    print CAST(@StartTime AS DateTime)
    print CAST(@EndTime AS DateTime)
    print @Getdate
    END
    Last edited by corncrowe; 03-14-12 at 16:52.

  3. #3
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by corncrowe View Post
    Probably because you are doing everything NOT between the two times.
    That's exactly what it was intended for. Do everything but stop between 1AM-6AM. However, it still runs.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Another thing to mention, in SQL 2008 and above, you can use the TIME datatype to simplify a little.
    Code:
    declare @time time
    
    set @time = getdate()
    
    select @time
    and corncrowe, you gotta do what you gotta do to keep healthy ;-).

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    BEGIN --  sproc
    
    IF DatePart(hour, GetDate()) BETWEEN 1 AND 5 RETURN
    
    --  Rest of proc goes here
    
    END  -- sproc
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by Pat Phelan View Post
    Code:
    BEGIN --  sproc
    
    IF DatePart(hour, GetDate()) BETWEEN 1 AND 5 RETURN
    
    --  Rest of proc goes here
    
    END  -- sproc
    -PatP
    Kewl! I'll try this one - thanks!

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by ARPRINCE View Post
    Kewl! I'll try this one - thanks!
    But you asked why the code between start and end time executed? Did you get that answered? Do you want to know why?

    I changed the time to fall between the ranges supplied and the code did not execute. So are you sure the code excuted?

    --------------
    test
    --------------
    DECLARE @StartTime AS VARCHAR(100)
    DECLARE @EndTime AS VARCHAR(100)
    DECLARE @Getdate as datetime
    SET @Getdate = convert(varchar(10),'2012-03-14') + ' 05:59AM'
    SET @StartTime = CONVERT(varchar(10),GETDATE(),121) + ' 01:00AM'
    SET @EndTime = CONVERT(varchar(10),GETDATE(),121) + ' 06:00AM'

    IF @Getdate NOT BETWEEN @StartTime AND @EndTime
    BEGIN
    print CAST(@StartTime AS DateTime)
    print CAST(@EndTime AS DateTime)
    print @Getdate
    END

    Just curious....

  8. #8
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Actually when I was testing the original code I posted, it was working fine. That's why I was confused why it keeps on running. The reason why I know it keeps on running is because I keep on receiving a report between 1-6AM.

    So basically, the whole scenario is this:

    I have a SSRS subscription report that is emailed out hourly everyday. Works fine. Now management came back and told me they don't want to receive the report between 1AM to 6AM. Fine, I thought. Problem is, SSRS 2005 can only have hourly subscription with no option to stop at a certain time.

    To try and solve this issue is a 2 step process.

    (1) I created a data driven subscription where the email address is read from a SQL table [DataSubscription] instead of typing it directly on the subscription configuration page. I then have a SSIS package that executes the SPROC every 55 mins of the hour. It truncates the [DataSubscription] and if the time is NOT between 1-6AM, it generates the report data and finally, adds email records into the [DataSubscription] table.

    (2) Every top of the hour, my SSRS subscription runs. Since it is data driven, when it finds that there is no email records in the [DataSubscription] table between 1-6AM, the report is not emailed out.

    Sorry....long story.

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by ARPRINCE View Post
    Actually when I was testing the original code I posted, it was working fine. That's why I was confused why it keeps on running. The reason why I know it keeps on running is because I keep on receiving a report between 1-6AM.

    So basically, the whole scenario is this:

    I have a SSRS subscription report that is emailed out hourly everyday. Works fine. Now management came back and told me they don't want to receive the report between 1AM to 6AM. Fine, I thought. Problem is, SSRS 2005 can only have hourly subscription with no option to stop at a certain time.

    To try and solve this issue is a 2 step process.

    (1) I created a data driven subscription where the email address is read from a SQL table [DataSubscription] instead of typing it directly on the subscription configuration page. I then have a SSIS package that executes the SPROC every 55 mins of the hour. It truncates the [DataSubscription] and if the time is NOT between 1-6AM, it generates the report data and finally, adds email records into the [DataSubscription] table.

    (2) Every top of the hour, my SSRS subscription runs. Since it is data driven, when it finds that there is no email records in the [DataSubscription] table between 1-6AM, the report is not emailed out.

    Sorry....long story.
    Question: When the report is sent between 1:00 and 6:00am is there data associated with the report or just an empty email?

  10. #10
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by corncrowe View Post
    Question: When the report is sent between 1:00 and 6:00am is there data associated with the report or just an empty email?
    The report is in excel format attachment. There is data and it is incremental.

    One thing weird however is I modified the same script this morning where instead of 1-6AM, I placed 1-10AM to test it. So I did not receive 9 and the 10 AM report. So I'm really at a lost why I received the report from 1-6.

    So I changed the script to use the DATEPART. I'm currently getting the reports every hour as designed. I'll know tomorrow if it works. If it does, I'll never touch it again!



    ---------------------------------------

    TRUNCATE TABLE DataSubscription;

    -- DECLARE @StartTime AS VARCHAR(100)
    -- DECLARE @EndTime AS VARCHAR(100)
    -- SET @StartTime = CONVERT(CHAR(10),GETDATE(),101) + ' 01:00AM'
    -- SET @EndTime = CONVERT(CHAR(10),GETDATE(),101) + ' 06:00AM'

    IF DATEPART(HOUR, GETDATE()) BETWEEN 7 AND 24
    BEGIN
    --- Do stuff here.
    END
    Last edited by ARPRINCE; 03-14-12 at 20:30.

  11. #11
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by ARPRINCE View Post
    The report is in excel format attachment. There is data and it is incremental.

    One thing weird however is I modified the same script this morning where instead of 1-6AM, I placed 1-10AM to test it. So I did not receive 9 and the 10 AM report. So I'm really at a lost why I received the report from 1-6.

    So I changed the script to use the DATEPART. I'm currently getting the reports every hour as designed. I'll know tomorrow if it works. If it does, I'll never touch it again!



    ---------------------------------------

    TRUNCATE TABLE DataSubscription;

    -- DECLARE @StartTime AS VARCHAR(100)
    -- DECLARE @EndTime AS VARCHAR(100)
    -- SET @StartTime = CONVERT(CHAR(10),GETDATE(),101) + ' 01:00AM'
    -- SET @EndTime = CONVERT(CHAR(10),GETDATE(),101) + ' 06:00AM'

    IF DATEPART(HOUR, GETDATE()) BETWEEN 7 AND 24
    BEGIN
    --- Do stuff here.
    END
    I am not a big fan of using NOT and between in the same sentence (criterion).

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To increase your warm and fuzzies:
    Code:
    DECLARE @today DATETIME = DateAdd(day, DateDiff(day, 0, GetDate()), 0)
    
    SELECT d
    ,  CASE
          WHEN DATEPART(Hour, d) BETWEEN 1 AND 6 THEN 'Die, quietly'
          ELSE 'RUN, LOUDLY!'
       END AS WatchaGonnaDo
       FROM (SELECT DATEADD(hour, number, @today) AS d
          FROM master.dbo.spt_values AS z1
          WHERE  'P' = z1.type
             AND z1.number BETWEEN 0 AND 23) AS z2
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by ARPRINCE View Post
    So I changed the script to use the DATEPART. I'm currently getting the reports every hour as designed. I'll know tomorrow if it works. If it does, I'll never touch it again!
    So the last report generated was 11:05PM and then restarted getting it again 7:05AM. Thanks to all!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •