Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Unanswered: Getting error with date conversion in WHERE clause

    Hi,

    I'm trying to pull a list of users with upcoming or recently passed birthdays. I'm using a CAST to create a birthday of the person using their actual birth month and day, but substituting the current day. This CAST works just fine in the actual query, but fails when I put it in the WHERE clause with the error: Conversion failed when converting date and/or time from character string. I don't see how it can work in one area and not in the other.

    Does anyone see what I'm doing wrong?

    Thanks!

    Code:
    SELECT 
    P.EMF_EMPL_ID,
    CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '/' + CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) AS Birthday, 
    DateDiff(d, getdate(), CAST(CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '/' + CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) + '/' + CAST(Year(getdate()) AS varchar) AS date)) AS DaysUntilBirthday
    FROM DeltekData..PR01EMF P
    WHERE 
    DateDiff(d, CAST(CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '/' + CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) + '/' + CAST(Year(getdate()) AS varchar) AS date), getdate()) BETWEEN -7 AND 14

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    This works for me:

    Code:
    SELECT 
       CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '/' + 
       CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) AS Birthday, 
       DateDiff(d, getdate(), 
          CAST(CAST(Year(GetDate()) AS varchar) + '-' + 
          CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '-' + 
          CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) AS date)) AS DaysUntilBirthday
     FROM  DeltekData..PR01EMF P
     WHERE DateDiff(d, getdate(), 
        CAST(CAST(Year(GetDate()) AS varchar) + '-' + 
        CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '-' + 
        CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) AS date)) 
    BETWEEN  -7 and 14

    ANSI Dates are formatted as yyyy-mm-dd. If you want to use / for display purposes, that is just fine. But when I do date compares and math, I would use the - to separate the elements into yyyy-mm-dd.

    Also your DateDiff parameters are reversed.
    First call was DateDiff(d, GetDate(), CAST())
    then the where had DateDiff(d, CAST(), GetDate)

    That may have had something to do with the unexpected results.
    Last edited by LinksUp; 05-15-13 at 20:54.

  3. #3
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    I tried copying and pasting your code exactly and it failed for me with the same error

    Quote Originally Posted by LinksUp View Post
    This works for me:

    Code:
    SELECT 
       CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '/' + 
       CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) AS Birthday, 
       DateDiff(d, getdate(), 
          CAST(CAST(Year(GetDate()) AS varchar) + '-' + 
          CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '-' + 
          CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) AS date)) AS DaysUntilBirthday
     FROM  DeltekData..PR01EMF P
     WHERE DateDiff(d, getdate(), 
        CAST(CAST(Year(GetDate()) AS varchar) + '-' + 
        CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '-' + 
        CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) AS date)) 
    BETWEEN  -14 and 7

    ANSI Dates are formatted as yyyy-mm-dd. If you want to use / for display purposes, that is just fine. But when I do date compares and math, I would use the - to separate the elements into yyyy-mm-dd.

    Also your DateDiff parameters are reversed.
    First call was DateDiff(d, GetDate(), CAST())
    then the where had DateDiff(d, CAST(), GetDate)

    That may have had something to do with the unexpected results.

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    WHERE 
        DateDiff(d, 
                 DATEADD(YEAR, 
                         DATEDIFF(YEAR, P.EMF_BIRTH_DATE8, GETDATE()),
                         P.EMF_BIRTH_DATE8), 
                 GETDATE()) BETWEEN -7 AND 14
    Hope this helps.

  5. #5
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Did you try Imex's suggestion? His WHERE clause worked for me.

    If his WHERE clause does not work, you will need to post the exact query you are using and the exact error message you are getting. The error message should have a line number where it thinks the error is occurring.

    It also would not hurt to post a small amount of DDL of the table you are working with and DML Inserts to help pinpoint the problem if it persists.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use something like:
    Code:
    DECLARE @d DATETIME = '1941-12-07'
    
    SELECT @d AS OriginalDate, d AS CurrentDate
    ,  CASE
          WHEN d <= DateAdd(year, DateDiff(year, @d, d), @d)
             THEN DateAdd(year,     DateDiff(year, @d, d), @d)
          ELSE    DateAdd(year, 1 + DateDiff(year, @d, d), @d)
       END AS NextAnniversary
    ,  DateDiff(day, d, CASE
          WHEN d <= DateAdd(year, DateDiff(year, @d, d), @d)
             THEN DateAdd(year,     DateDiff(year, @d, d), @d)
          ELSE    DateAdd(year, 1 + DateDiff(year, @d, d), @d)
       END) AS delta
       FROM (SELECT DateAdd(day, number, '2010-01-01') AS d
          FROM master.dbo.spt_values
    	  WHERE  'P' = type) AS z
       WHERE  DateDiff(day, d, CASE
          WHEN d <= DateAdd(year, DateDiff(year, @d, d), @d)
             THEN DateAdd(year,     DateDiff(year, @d, d), @d)
          ELSE    DateAdd(year, 1 + DateDiff(year, @d, d), @d)
       END) NOT BETWEEN 15 AND 358
    Which is actually just a longer version of:
    Code:
    DECLARE @d DATETIME = '1941-12-07'
    
    SELECT @d AS OriginalDate, d AS CurrentDate
    ,  NextAnniversary
    ,  DateDiff(day, d, NextAnniversary) AS delta
       FROM (SELECT DateAdd(day, number, '2010-01-01') AS d
    ,     CASE
             WHEN DateAdd(day, number, '2010-01-01') <= DateAdd(year, DateDiff(year, @d, DateAdd(day, number, '2010-01-01')), @d)
                THEN DateAdd(year,     DateDiff(year, @d, DateAdd(day, number, '2010-01-01')), @d)
             ELSE    DateAdd(year, 1 + DateDiff(year, @d, DateAdd(day, number, '2010-01-01')), @d)
          END AS NextAnniversary
          FROM master.dbo.spt_values
    	  WHERE  'P' = type) AS z
       WHERE  DateDiff(day, d, NextAnniversary) NOT BETWEEN 15 AND 358
    -PatP
    Last edited by Pat Phelan; 05-16-13 at 14:47.
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    This worked, thanks so much!

    Quote Originally Posted by imex View Post
    Hi,

    Try:

    Code:
    WHERE 
        DateDiff(d, 
                 DATEADD(YEAR, 
                         DATEDIFF(YEAR, P.EMF_BIRTH_DATE8, GETDATE()),
                         P.EMF_BIRTH_DATE8), 
                 GETDATE()) BETWEEN -7 AND 14
    Hope this helps.

Posting Permissions

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