Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Answered: Assistance with Dateadd and DateDiff

    PHP Code:
    DATEADD(MONTHDATEDIFF(MONTH0A.INSERT_DATE), 0
    This get's me the first day of the month but brings over a time component. Obviously Cast as Date will take care of that, but I am having a hard time incorporating the cast function in this query.

    Thoughts?
    Last edited by VLOOKUP; 07-31-15 at 17:52.

  2. Best Answer
    Posted by Pat Phelan

    "Play with this one a bit:
    Code:
    WITH step1 AS (
    SELECT
       DateAdd(s, Pi() * 1e5 * z.number, GetDate()) AS d -- Generate dates a day or two apart
       FROM master.dbo.spt_values AS z
       WHERE  'P' = z.type
    ), step2 AS (
    SELECT step1.d
    ,  Cast(step1.d AS DATE) AS myDateOnly
    ,  DateAdd(d, DateDiff(d, 0, step1.d), 0) AS myDateTime
       FROM step1
    )
    SELECT *
       FROM step2 AS a
       INNER JOIN step2 AS b
          ON (b.myDateOnly = a.myDateTime)
    -PatP"


  3. #2
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Disregard, I ended up inserting into a temp table, then joining on with a Cast( @Date, AS DATE) AS DATEFIELD

    The join worked fine and updated the records accordingly.



    Just curious does the date time and date matter when joining? I've never tried to do that to be honest. Hmmmmmm.
    Last edited by VLOOKUP; 07-31-15 at 18:25.

  4. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your original expression:
    Code:
    DATEADD(MONTH, DATEDIFF(MONTH, 0, A.INSERT_DATE), 0)
    will return the moment that the current month began. The time component will be 00:00:00.000 meaning precisely midnight. For any practical purposes, that means that there is no time component.

    If you compare datetime expressions in a join (for example a theta join), then the time is compared and considered.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat - I'm not sure I follow.

    So are you saying the date time joined against a date will join correctly if one of the columns is data time (set to 0) and the other is a pure date column. Of course the date portion on both are formatted correct. YYYY-MM-DD

    Thanks for all your help.

  6. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Play with this one a bit:
    Code:
    WITH step1 AS (
    SELECT
       DateAdd(s, Pi() * 1e5 * z.number, GetDate()) AS d -- Generate dates a day or two apart
       FROM master.dbo.spt_values AS z
       WHERE  'P' = z.type
    ), step2 AS (
    SELECT step1.d
    ,  Cast(step1.d AS DATE) AS myDateOnly
    ,  DateAdd(d, DateDiff(d, 0, step1.d), 0) AS myDateTime
       FROM step1
    )
    SELECT *
       FROM step2 AS a
       INNER JOIN step2 AS b
          ON (b.myDateOnly = a.myDateTime)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I'll give this a go tomorrow. Thanks for the suggestion Pat.

  8. #7
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I like how it gives me a table to select / join from. I'm not sure I understand what's going on in the beginning of the select. Maybe I should take an Algebra course again.

    Thanks again, I think I have a work around, but I'll save this snippet. Thanks again Pat.

    How many scotches are we up to now? Maybe I should just send you a bottle of Macellan 18 year for all your efforts.

  9. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I usually go through most of a four pack of Guiness Stout every year, but I'm not much of a drinker.

    I always hope that people will pass my help forward rather than pay it back. Technology moves too fast, and there are far too few new people (especially young people) getting into the field. There aren't enough now to fill the current needs and the gap between the geek supply and geek demand grows wider every day. Whatever we as a geek community can do to expand the geek community will make life better for our descendants.

    The base expression just takes pi and scales it up by an arbitrary real number (currently 10,000 or 1e5). That allows me to produce a datetime series that moves around the clock so that people don't get hung up on the time component because the time of day values are different. Pi is a nice irrational number, and scaling it is easy to produce whatever distribution suits your needs.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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