By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,440 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

smalldatetime convert problem

dcharnigo
P: 20
I am converting a varchar(10) field to a smalldatetime in a stored procedure but have run into a problem. Some of the rows contain an invalid date. varchar(10) format is mm/dd/yyyy some rows are filled with 00/00/0000 however causing the convert to fail. how can I get around this? Updating the table is not an option. My convert is as follows:
...
WHERE CONVERT(datetime, CIFExpDate) < CURRENT_TIMESTAMP
ORDER BY CONVERT(datetime, CIFExpDate) DESC
...

Thanks for any help!
Mar 19 '08 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
use the CASE FUNCTION and the
use ISDATE FUNCTION


-- CK
Mar 19 '08 #2

dcharnigo
P: 20
Ok Closer I got the select to work:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     CIFExpDate = 
  3.     CASE
  4.         WHEN ISDATE(CIFExpDate) = 1 THEN CONVERT(datetime, CIFExpDate)
  5.         ELSE CURRENT_TIMESTAMP
  6.     END
  7. FROM
  8.     zCIFRecord
  9.  
But when I add the WHERE clause it fails:
Expand|Select|Wrap|Line Numbers
  1. WHERE CIFExpDate < CURRENT_TIMESTAMP
This is obviously because the field is only being temporarily updated, so I assigned the values from the Select to a temp variable (calling a convert in the WHERE fails because of my original problems of 00/00/0000)

Expand|Select|Wrap|Line Numbers
  1. DECLARE @datestring datetime
  2. SELECT
  3.     @datestring = 
  4.     CASE
  5.         WHEN ISDATE(CIFExpDate) = 1 THEN CONVERT(datetime, CIFExpDate)
  6.         ELSE CURRENT_TIMESTAMP
  7.     END
  8. FROM
  9.     zCIFRecord
  10. WHERE @datestring < CURRENT_TIMESTAMP
  11.  
Then I get the the following error:

Expand|Select|Wrap|Line Numbers
  1. "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."
Mar 20 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Try these clause:


WHERE convert(case isdate(CIFExpDate) = 1 then
CONVERT(datetime, CIFExpDate) else NULL) < CURRENT_TIMESTAMP
ORDER BY convert(case isdate(CIFExpDate) = 1 then
CONVERT(datetime, CIFExpDate) else NULL)

Watch out for the time part. If you need to disregard the timepart, use DATEDIFF instead of simple less than.

-- CK
Mar 20 '08 #4

dcharnigo
P: 20
Thanks, that got me headed in the correct direction and I got it to work, here is the final code for anyone who might want to do something similar:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     CIFPan,
  3.     CIFMemNum,
  4.     CIFLName,
  5.     CIFFName,
  6.     CIFInitial,
  7.     CIFExpDate
  8. FROM
  9.     zCIFRecord
  10. WHERE
  11.     CASE 
  12.         WHEN ISDATE(CIFExpDate) = 1 
  13.         THEN CONVERT(datetime, CIFExpDate) 
  14.         ELSE NULL
  15.     END < CURRENT_TIMESTAMP
  16. ORDER BY 
  17.     CASE
  18.         WHEN ISDATE(CIFExpDate) = 1 
  19.         THEN CONVERT(datetime, CIFExpDate) 
  20.         ELSE NULL
  21.     END ASC
  22. GO
Mar 20 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.