Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Always show dates from begining of Month

    I have this function in my query

    Code:
    DATEDIFF(DAY, [Date on Waiting List], GETDATE())/ 7 AS [Weeks Waiting]

    and looking to find how I can change this and everytime I run the query it looks back to find the begining of the month, instead of todays date. So no matter what day I run the query in the month, it always reverts back and measures from the 1st...
    How is that possible to do that?

    From another thread in
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110634

    the reply was to use

    Code:
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
    I changed my Query and this error message appears when trying to execute the query

    Msg 257, Level 16, State 3, Line 1
    Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

    I dont understand what its saying, can you explain?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by JezLisle
    Code:
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
    I changed my Query and this error message appears when trying to execute the query

    Msg 257, Level 16, State 3, Line 1
    Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

    I dont understand what its saying, can you explain?
    Can you please post the EXACT code? There is nothing wrong with that line as such (so long as you precede it with "SELECT "). I suspect you are trying to update a column, or compare to another value but can't tell without the code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Take todays date and find first of the month
    Code:
    SELECT DateAdd(mm, DateDiff(mm, 0, GetDate()), 0)
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Take todays date and find first of the month
    Code:
    SELECT DateAdd(mm, DateDiff(mm, 0, GetDate()), 0)
    So....did you make it past the title?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    About half way down, yeah


    oops!
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    About half way down, yeah


    oops!

    georgev
    partial question assimilator
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And proud
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    My Query
    Code:
    SELECT DISTINCT 
                          [Waiting List Identifier], [Patient NHS Identifier], [Patient Name], [Waiting List Name], [Waiting List Description], [Date on Waiting List], 
                          [Referral Archive Flag], [Patient Archive Flag], WL_TYPE, [Referred to Team], DATEADD(MONTH, DATEDIFF(MONTH, 0, 
    	GETDATE()), 0) / 7 AS [Weeks Waiting],
    CASE 
    		WHEN
    				DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) / 7 < 4 
    		THEN 1 
    		WHEN
    				DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) / 7< 8 
    		THEN 2 
    		WHEN 
    				DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) / 7< 13 
    		THEN 3 
    		WHEN 
    				DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) / 7< 17
    		THEN 4
    		WHEN 
    				DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) / 7< 21
    		THEN 5
    		ELSE 6
    END AS [Weeks Range],
    CASE 
    		WHEN
    				DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) / 7 < 4 
    		THEN '0<4' 
    		WHEN
    				DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) / 7< 8 
    		THEN '4<8'
    		WHEN 
    				DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) / 7< 13 
    		THEN '8<13'
    		WHEN 
    				DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) / 7< 17
    		THEN '13<17'
    		WHEN 
    				DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) / 7< 21
    		THEN '17<21'
    		ELSE  '21+'
    END AS [Weeks Range Title]
    FROM         dbo.REP_OP_WAITING_LIST
    WHERE     ([Waiting List Name] = 'cott') AND ([Date Removed from Waiting List] IS NULL) OR
                          ([Waiting List Name] = 'cotl') AND ([Date Removed from Waiting List] IS NULL)
    ORDER BY [Weeks Range]

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - this is a nice real life lesson in debugging T-SQL.

    If you think you know what the problem is, isolate it. If you had run:
    Code:
    SELECT    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
    You would have got back what you expected. You now know this isn't causing the error, so now you add a little bit more:
    Code:
    SELECT    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) / 7
    and it bombs with your exact error.

    The lessons? Always attempt to prove\ disprove your hypotheses - don't go with assumptions.
    Also, build up your expressions incrementally and test at each stage.

    Anyhoo - what are you trying to do with your CASE expression (in natural English)? Once we figure that we can fix the code up.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To help to make your query cleaner and easier to read, why not also set up a variable to hold the repeated value?
    Code:
    DECLARE @first_day_of_month datetime
       SET @first_day_of_month = DateAdd(mm, DateDiff(mm, 0, GetDate()), 0)
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    What I am trying to do is show the number of weeks from the Date on Waiting list to the 1st of Current Month as a number, then I want to show that number as my Weeks Range, 1 to 6 and also Group it by the 0<4 and so on.

    So I want to run the report looking back at the 1st of the month regardless of what day I run it on, it still looks back to the 1st.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - so use Geroge's code above:
    Code:
    ...WHEN DATEDIFF(ww, DateOnWaitingList, @first_day_of_month) < 4 THEN...
    this might need some tweaking but let's see how we do.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I've changed the SQL to this below and still I get a error message,

    Code:
    DECLARE @FirstDayOfMonth DATETIME
    SET @FirstDayOfMonth =DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
    SELECT DISTINCT 
                          [Waiting List Identifier], [Patient NHS Identifier], [Patient Name], [Waiting List Name], [Waiting List Description], [Date on Waiting List], 
                          [Referral Archive Flag], [Patient Archive Flag], WL_TYPE, [Referred to Team], @FirstDayOfMonth / 7 AS [Weeks Waiting],
    CASE 
    		WHEN
    				DATEDIFF(ww, DateOnWaitingList, @first_day_of_month) < 4
    		THEN 1 
    		WHEN
    				DATEDIFF(ww, DateOnWaitingList, @first_day_of_month) < 8 
    		THEN 2 
    		WHEN 
    				DATEDIFF(ww, DateOnWaitingList, @first_day_of_month) < 13 
    		THEN 3 
    		WHEN 
    				DATEDIFF(ww, DateOnWaitingList, @first_day_of_month) < 17
    		THEN 4
    		WHEN 
    				DATEDIFF(ww, DateOnWaitingList, @first_day_of_month) < 21
    		THEN 5
    		ELSE 6
    END AS [Weeks Range],
    CASE 
    		WHEN
    				DATEDIFF(ww, DateOnWaitingList, @first_day_of_month) < 4 
    		THEN '0<4' 
    		WHEN
    				DATEDIFF(ww, DateOnWaitingList, @first_day_of_month) < 8 
    		THEN '4<8'
    		WHEN 
    				DATEDIFF(ww, DateOnWaitingList, @first_day_of_month) < 13 
    		THEN '8<13'
    		WHEN 
    				DATEDIFF(ww, DateOnWaitingList, @first_day_of_month) < 17
    		THEN '13<17'
    		WHEN 
    				DATEDIFF(ww, DateOnWaitingList, @first_day_of_month) < 21
    		THEN '17<21'
    		ELSE  '21+'
    END AS [Weeks Range Title]
    FROM         dbo.REP_OP_WAITING_LIST
    WHERE     ([Waiting List Name] = 'cott') AND ([Date Removed from Waiting List] IS NULL) OR
                          ([Waiting List Name] = 'cotl') AND ([Date Removed from Waiting List] IS NULL)
    ORDER BY [Weeks Range]
    Error Message
    Msg 257, Level 16, State 3, Line 4
    Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

    Is it me misunderstanding something or am i just not reading right.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I can see the cause, but how about you apply the technique in post #9 to identify the problem? i.e. strip the thing down to the barest essentials and build it up slowly until it errors.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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