• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

case expression with date rages

I am trying to use a case expression with date rages, when i use the cast function I keep getting the
“Conversion failed when converting date and/or time from character string” error. I can only query the database not able to create any temp table.





select B.DisplayName ,count(ORDER_RSN)
,CASE
WHEN ORDER_COMPLETE_DTE BETWEEN Cast('2013-08-22'AS Date) AND Cast ('2013-09-21' AS Date) THEN 'SEP_2013'
--WHEN ORDER_COMPLETE_DTE BETWEEN Cast('2013-09-22' AS Date) AND Cast ('2013-10-21' AS date) THEN 'OCT_2013'
--WHEN ORDER_COMPLETE_DTE BETWEEN Cast('2013-10-22' AS Date) AND Cast ('2013-11-21' AS date) THEN 'NOV_2013'
--WHEN ORDER_COMPLETE_DTE BETWEEN Cast('2013-11-22' AS Date) AND Cast ('2013-12-21' AS date) THEN 'DEC_2013'
--WHEN ORDER_COMPLETE_DTE BETWEEN Cast('2013-12-22' AS Date) AND Cast ('2014-01-21' AS date) THEN 'JAN_2014'
ELSE 'Unknown'
END AS Fiscal_Month
FROM [bsWRK].[dbo].[WODM_ICOMP_Orders] A
  Join [Employee].[dbo].[vAllUsers] B
  on A.PERNR = B.PerNr
0
onaled777
Asked:
onaled777
  • 2
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>ORDER_COMPLETE_DTE
What's the data type for this column?  If it's anything other than date, based on the above T-SQL the first time it hits a non-date value it's going to throw that error.

Just for kicks and giggles, try this...

SELECT blah, blah, blah,
   CASE ISDATE(ORDER_COMPLETE_DTE)
     WHEN 1 then   -- Insert your big honkin' CASE block here
     WHEN 0 then 'Not a date - need to figure out what to do here
   END,
-- the rest of your query here

Also

WHEN ORDER_COMPLETE_DTE BETWEEN Cast('2013-10-22' AS Date) AND Cast ('2013-11-21' AS date)

Can probably just do ... BETWEEN '2013-10-22' AND '2013-11-21'
0
 
onaled777Author Commented:
ORDER_COMPLETE_DTE is Date Type. When I run the ISDATE query I get the following error
"Argument data type date is invalid for argument 1 of isdate function"
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>CASE ISDATE(ORDER_COMPLETE_DTE)
>"Argument data type date is invalid for argument 1 of isdate function"
Fascinating.  Some possibilities are...
there is a typo in the ORDER_COMPLETE_DTE column name
that column is not in either of the tables in your FROM clause
Gremlins
>select B.DisplayName ,count(ORDER_RSN)
btw the GROUP BY B.DisplayName, ORDER_COMPLETE_DTE line is missing from the bottom of your query.
0
 
Scott PletcherSenior DBACommented:
Format YYYY-MM-DD is not 100% safe.  Use YYYYMMDD, which always works, no matter the date and/or language settings.  You don't need or want to cast it as date, since varchar has a lower precedence than the column and the column type might change later.  Finally, always use >= and < rather than BETWEEN with dates/datetimes to make sure you don't miss data or include non-matching data.

Finally, ISDATE() would never be needed on a column that's of datatype date or datetime, since it must be that data type to be stored in a column with that data type!  Likewise, you wouldn't need a check to verify that integer columns contained integer values :-) .


select B.DisplayName ,count(ORDER_RSN)
,CASE
WHEN ORDER_COMPLETE_DTE >= 20130822' AND ORDER_COMPLETE_DTE < '20130922' THEN 'SEP_2013'
WHEN ORDER_COMPLETE_DTE >= 20130922' AND ORDER_COMPLETE_DTE < '20131022' THEN 'OCT_2013'
WHEN ORDER_COMPLETE_DTE >= '20131022' AND ORDER_COMPLETE_DTE < '20131122' THEN 'NOV_2013'
--...
ELSE 'Unknown'
END AS Fiscal_Month
FROM [bsWRK].[dbo].[WODM_ICOMP_Orders] A
  Join [Employee].[dbo].[vAllUsers] B
  on A.PERNR = B.PerNr
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now