How do I test a CAST conversion will work when it is used in a WHERE statement?

How do I test a CAST conversion will work when it is used in a WHERE statement?

SELECT a.month, a.year from gift_recipient
WHERE
      datediff(dd,convert(datetime,cast(year(getdate()) as varchar)+'/'+cast(month(getdate()) as varchar)+'/'+cast(day(getdate()) as varchar),111) ,convert(datetime,cast(a.year as varchar)+'/'+cast(a.month as varchar)+'/'+cast(a.date as varchar),111) ) = 14

Unfortunately, when the returned a.month = 0 (as it can be), the CAST fails with
'Conversion failed when converting datetime from character string.'

When a 0 is encountered (default mont/ year), I'd like to set the condition 0=14  so that it fails gracefully.

philwill4uAsked:
Who is Participating?
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Just a thought - if your a.year, a.month and a.date fields are all ints, check this conversion out

declare @day int
declare @month int
declare @year int

select rand( checksum( newid() ))
 
select
      @day = rand() * 30 + 1
      , @month = rand() * 11 + 1
      , @year = rand() * 10 + 2000

select @day, @month, @year

select dateadd( day, @day - 1, dateadd( month, @month - 1, dateadd( year, @year - 1900, 0 )))

Regards
  David
0
 
philwill4uAuthor Commented:
I should say the CONVERT fails
0
 
QPRCommented:
Is 0 acceptable for your business practices? Is this something you could validate in an application?
Anyways if 0 is allowable then you could try replace(a.month,0,14)
Or if you are getting null then isnull(a.month,14)

That said I would imagine you'd get the exact same error given that there is no 14th month.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
philwill4uAuthor Commented:
It's a field where one or more of the date parts (day, month, year) has not been provided/ given.  
Year is always optional.  Day and Month are also optional but if there is a Day, there is always a Month.

Can I wrap an IF... THEN.... ELSE around the WHERE clause?


0
 
philwill4uAuthor Commented:
The 14 is the datediff.... it's trying to calculate whether the date is 14 months away.
0
 
philwill4uAuthor Commented:
14 days away
0
 
QPRCommented:
then would they not be NULL or does the field have a default value of 0?
Not sure about the if then else in WHERE but I have used a case statement there before where the WHERE was the value of a @parameter in a stored procedure
0
 
philwill4uAuthor Commented:
It defaults as 0.

If   a.YEAR = 0 then I'd assume it is this year (2007)
If   a.month = 0 OR a.date = 0 then I'd like to return nothing as there is no valid date to compare for that record.

Trouble is... how do I get that logic into the SQL statement?

0
 
David ToddSenior DBACommented:
Hi,

For starters, why write

SELECT a.month, a.year from gift_recipient
WHERE
      datediff(
            dd
              , convert(datetime,cast(year(getdate()) as varchar)+'/'+cast(month(getdate()) as varchar)+'/'+cast(day(getdate()) as varchar),111)
            , convert(datetime,cast(a.year as varchar)+'/'+cast(a.month as varchar)+'/'+cast(a.date as varchar),111)
      ) = 14


instead of

SELECT a.month, a.year from gift_recipient
WHERE
      datediff(
            dd
              , getdate()
            , convert(datetime,cast(a.year as varchar)+'/'+cast(a.month as varchar)+'/'+cast(a.date as varchar),111)
      ) = 14

Why 14 days?

I'd suggest changing it to futureDate between getdate() and getdate + 14 days.

HTH
  David
0
 
David ToddSenior DBACommented:
Hi,

What is the result set from this code?

select
      case
            when a.year not in( 2007, 2008 ) then year( getdate() )
            else a.year
      end ayear
      , case
            when not a.month between 1 and 12 then month( getdate() )
            else a.month
      end amonth
      , case
            when not a.date between 1 and 31 then day( getdate() )
            else a.date
      end aday
from dbo.gift_recipient

HTH
  David
0
 
philwill4uAuthor Commented:
The server is in the US and uses mm/dd/yyyy format.  I can't change this.  We are in Australia and use dd/mm/yyyy format.  So the conversion of the getdate appears necessary to get the datediff function to co-operate.

14 days is the period we look ahead to trigger an event today (eg. an email)
0
 
David ToddSenior DBACommented:
Hi,

Try this:

      ab.amonth
      , ab.ayear
from
      (
      select
            case
                  when a.year not in( 2007, 2008 ) then year( getdate() )
                  else a.year
            end ayear
            , case
                  when not a.month between 1 and 12 then month( getdate() )
                  else a.month
            end amonth
            , case
                  when not a.date between 1 and 31 then day( getdate() )
                  else a.date
            end aday
      from dbo.gift_recipient a
      ) ab
where
      datediff(
          dd
          , getdate()
          , convert( date
                  , cast( ab.ayear as varchar( 4 ))
                        + right( '0' + cast( ab.amonth as varchar( 2 )), 2 )
                        + right( '0' + cast( ab.aday as varchar( 2 )), 2 )
      ) between 0 and 14

Its not foolproof - February, April, June, September, November can still be tripped up,

Regards
  David

PS Would suggest storing dates as dates instead of years and months and days.

If want to double check the conversion, then use isdate() function
0
 
David ToddSenior DBACommented:
Hi,

>> The server is in the US and uses mm/dd/yyyy format.

That is one reason I prefer the ISO date formats of YYY-MM-DD or yyyymmdd. The americans can't misread it ... :-)

Regards
  David
0
 
philwill4uAuthor Commented:
I'll give it a shot.... and yeap... I agree ISO is far better but as it doesn't have US in it, I'm sure it will become USISO in time ;-)  ... it's all about US US US US :-) and not EU EU EU EU  :: lol

The tables had been developed before I got my hands on it and a lot of code has been built around it so I'm kind of stuck for now to get it working.

0
 
philwill4uAuthor Commented:
Like the conversion.... I'll check to see how I can use it in the code
0
 
dbbishopConnect With a Mentor Commented:
How about:

SELECT a.month, a.year from gift_recipient
WHERE
    CASE WHEN a.month = 0 THEN 14 ELSE
      datediff(dd,convert(datetime,cast(year(getdate()) as varchar)+'/'+cast(month(getdate()) as varchar)+'/'+cast(day(getdate()) as varchar),111) ,convert(datetime,cast(a.year as varchar)+'/'+cast(a.month as varchar)+'/'+cast(a.date as varchar),111) ) END = 14
0
 
philwill4uAuthor Commented:
Great... we're almost there with a combination of answers

The code now looks like...
SELECT a.month, a.year from gift_recipient
WHERE
    CASE
            WHEN a.month = 0 THEN 15    /* no valid date */
      ELSE
            datediff(dd
         ,getdate()
         ,dateadd( day, a.date - 1, dateadd( month, a.month - 1, dateadd( year, a.year - 1900, 0 )))
                 )             
      END = 14  /* 14 days notice */


Only problem is with a.year.  If a.year = 0 and a.month > 0 then a.year = 2007....
  and continue with      ......     datediff(dd,getdate() ,dateadd( day, a.date - 1, dateadd( month, a.month - 1, dateadd( year, a.year - 1900, 0 )))

Probably just another case statement

0
 
David ToddSenior DBACommented:
Hi,

Try
SELECT a.month, a.year from gift_recipient
WHERE
    CASE
            WHEN a.month = 0 or a.year = 0 THEN 15    /* no valid date */
      ELSE
            datediff(dd
         ,getdate()
         ,dateadd( day, a.date - 1, dateadd( month, a.month - 1, dateadd( year, a.year - 1900, 0 )))
                 )            
      END = 14  /* 14 days notice */

HTH
  David
0
 
philwill4uAuthor Commented:
And here is the final code thanks to you guys..... it's working perfectly :)


SELECT a.date, a.month, a.year from gift_recipient
WHERE
    CASE
            WHEN a.month = 0 THEN 15
            WHEN a.year = 0 AND a.month > 0 THEN (
                  datediff(dd
                  ,getdate()
                  ,dateadd( day, a.date - 1, dateadd( month, a.month - 1, year(getdate()))))
                 )                   
      ELSE
            datediff(dd
         ,getdate()
         ,dateadd( day, a.date - 1, dateadd( month, a.month - 1, dateadd( year, a.year - 1900, 0 )))
                 )             
      END = 14  /* 14 days notice */
0
 
philwill4uAuthor Commented:
I'll fix up year(getdate()) to look ahead 14 days so I can look ahead into the next year.  

Many thanks for you help ;-)
0
 
David ToddSenior DBACommented:
Hi,

As above, What if a holiday or non-business day is the exact 14 days? I woudl suggest a range so that if a run is missed - the sending the email for instance - that it is picked up on the subsequent run.

In that case you would need a sent email flag which you can set so that the row is ignored in the second run ...

HTH
  David
0
 
philwill4uAuthor Commented:
Good thinking.... but as it will be automated to run through ASP code each day, the problem shouldn't occur.  The ASP code sets a flag when the email goes out, so if a day is ever missed (eg. systems fail) then a refresh will pick up the outstanding items.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.