[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-07-29
22
Medium Priority
?
212 Views
Last Modified: 2010-03-19
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.

0
Comment
Question by:philwill4u
  • 12
  • 7
  • 2
  • +1
22 Comments
 

Author Comment

by:philwill4u
ID: 19589767
I should say the CONVERT fails
0
 
LVL 29

Expert Comment

by:QPR
ID: 19589802
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
 

Author Comment

by:philwill4u
ID: 19589829
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:philwill4u
ID: 19589832
The 14 is the datediff.... it's trying to calculate whether the date is 14 months away.
0
 

Author Comment

by:philwill4u
ID: 19589839
14 days away
0
 
LVL 29

Expert Comment

by:QPR
ID: 19589844
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
 

Author Comment

by:philwill4u
ID: 19589862
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
 
LVL 35

Expert Comment

by:David Todd
ID: 19589902
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
 
LVL 35

Expert Comment

by:David Todd
ID: 19589921
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
 

Author Comment

by:philwill4u
ID: 19589935
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
 
LVL 35

Expert Comment

by:David Todd
ID: 19589945
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
 
LVL 35

Expert Comment

by:David Todd
ID: 19589950
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
 
LVL 35

Accepted Solution

by:
David Todd earned 1000 total points
ID: 19589984
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
 

Author Comment

by:philwill4u
ID: 19589986
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
 

Author Comment

by:philwill4u
ID: 19589998
Like the conversion.... I'll check to see how I can use it in the code
0
 
LVL 15

Assisted Solution

by:dbbishop
dbbishop earned 1000 total points
ID: 19590030
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
 

Author Comment

by:philwill4u
ID: 19590065
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
 
LVL 35

Expert Comment

by:David Todd
ID: 19590071
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
 

Author Comment

by:philwill4u
ID: 19590082
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
 

Author Comment

by:philwill4u
ID: 19590088
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
 
LVL 35

Expert Comment

by:David Todd
ID: 19590094
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
 

Author Comment

by:philwill4u
ID: 19590101
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question