Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2014
    Posts
    5

    Question Unanswered: Certain date of a month

    Hi All,

    I'm trying to write a query that will tell me the day of the week for the 20th of any given month. Any help would be appreciated.

    B.I. Cal

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Calendar tables are awesome - everyone should have one

    http://gvee.co.uk/files/sql/dbo.numb...o.calendar.sql
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I agree with gvee, a calendar table is exactly what you need and especially for doing BI.

    With that said, a quick SQL statement to generate/test what you need would be:
    Code:
    WITH cte AS (
       SELECT DateAdd(month, number, '2000-01-28') AS d
          FROM master.dbo.spt_values
    	  WHERE  'P' = type
    )
    SELECT d, DateName(dw, DateAdd(day, 20 - DatePart(day, d), d))
       FROM cte
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2014
    Posts
    5

    Talking

    healdem,

    Thanks for your reply. I know about most of those date functions and have used them. You not giving me the silver bullet made me rethink some things and I figured out what I needed to do.

    gvee and Pat Phelan,

    Thanks for y'alls replies too, but for now I don't need a date table, but will definitely look into one in the future.

    BI_Cal

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry, my bad. I had presumed that you were capable of reading and comprehending the references to extract what you need. Have a look at datepart and or datename.
    Last edited by healdem; 07-22-14 at 05:45.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2014
    Posts
    5

    Thumbs up

    healdem,

    Sorry for the misunderstanding. I'm capable of reading and comprehending the references. I got what I needed by using convert(), datePart() and dateName(). I rethought how I was previously trying to use the functions. Things are all good now. Thanks

    BI_Cal

Posting Permissions

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