+ Reply to Thread
Results 1 to 2 of 2

Thread: Using Sysdate

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    15

    Using Sysdate

    I am creating a query that runs twice a month,
    1) On the 5th of the month looking at billing days 16-last day of the month of the previous month.
    Code:
    datepart(dd,h.BILLED_DATE) > 15    
    DATEPART(mm, h.billed_date) = DATEPART(mm,dateadd(m,-1,getdate()))
    and DATEPART(yyyy, h.billed_date) = DATEPART(yyyy,dateadd(m,-1,getdate()))
    2) On the 20th of the month looking at billing days of 1-15 of the same month.
    Code:
    datepart(dd,h.BILLED_DATE) >= 1
    and datepart(dd,h.BILLED_DATE) < 16
    and DATEPART(mm, h.BILLED_DATE) = DATEPART(mm,GETDATE())
    and DATEPART(yyyy, h.BILLED_DATE) = DATEPART(yyyy,GETDATE()))
    And using that where statment , i get error :
    Code:
    Where
      Case 
        when datepart(dd,getdate()) > 15 
          then [2 above]
          else [1 above]
      End

  2. #2
    Junior Member
    Join Date
    May 2012
    Posts
    6
    CASE is an expression that returns a single value. You can't use it for control of flow like you can in some other languages.
    Here is the working one :
    Code:
    DECLARE @today SMALLDATETIME, @start SMALLDATETIME, @end SMALLDATETIME;
    SET @today = DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP));
    
    SET @start = DATEADD(DAY, 1-DAY(@today), @today);
    SET @end = DATEADD(DAY, 15, @start);
    
    IF DATEPART(DAY, @today) <= 15
    BEGIN
        SET @start = DATEADD(MONTH, -1, @end);
        SET @end = DATEADD(DAY, 1-DAY(@today), @today);
    END 
    
    SELECT ... WHERE h.billed_date >= @start AND h.billed_date < @end;

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

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