Results 1 to 2 of 2
  1. #1
    Join Date
    May 2014
    Posts
    2

    Unhappy Unanswered: Count Number of Days between Current Date and DateColumn

    Hi There I'm having a problem counting the number of Dates between a Column ALIAS PROJECTED_DATE and The Current Date. The result is always NULL in my column.

    Here is the part of my MYSql Query with the ALIAS field, This Fields works, My Date ADDED_DATE is actually a VARCHAR so I use STR_TO_DATE.

    Code:
    DATE_FORMAT(DATE_ADD(STR_TO_DATE(ADDED_Date,'%d/%m/%Y'), INTERVAL 30 DAY),'%d/%m/%Y') AS PROJECTED_Date,

    Here is my DATEDIFF and I have tried several as you can see but they all evaluate to NULL and do not give the count of DAYS between the Expected_DATE and CURDATE()

    ALL of these that I have Tried always return NULL in the DELTA Field and not the number of Days between CURDATE() and PROJECTED_DATE

    Code:
    DATEDIFF(CURDATE(), Expected_Date) AS NUM_DAYS,
    DATE_FORMAT(DATEDIFF(CURDATE(), STR_TO_DATE(Projected_Date,'%m/%d/%Y')),'%m/%d/%Y') AS NUM_DAYSa,
    DATEDIFF(CURDATE(),STR_TO_DATE(Projected_Date,'%m/%d/%Y')) AS NUM_DAYSb,
    DATEDIFF(CURDATE(),DATE_FORMAT(Projected_Date,'%m/%d/%Y')) AS NUM_DAYSc,
    STR_TO_DATE(Expected_Date, '%d-%m-%Y') BETWEEN CURDATE() AND Projected_Date AS NUM_DAYSd,
    I just left all my attempts in to show you I had tried every way I know to get the number of days but all attempts are only putting NULL in my NUM_DAYS column.

    Regards and thanks

    Ironoverload.

  2. #2
    Join Date
    May 2014
    Posts
    1

    Smile Pay attention at date formats you use

    Hi Ironoverload,

    the problem is that you have 2 different date formats in your DATEDIFF function, because the date format resulted from CURDATE is 'YYYY-MM-DD'.

    You should use DATEDIFF with 2 dates written in the same time format.

    Try something like: DATEDIFF(CURDATE(),STR_TO_DATE(Projected_Date,'%y-%m-%d')).

    Hope it works

Tags for this Thread

Posting Permissions

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