473,383 Members | 1,798 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,383 software developers and data experts.

smalldatetime convert problem

dcharnigo
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
4 2415
ck9663
2,878 Expert 2GB
use the CASE FUNCTION and the
use ISDATE FUNCTION


-- CK
Mar 19 '08 #2
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
2,878 Expert 2GB
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
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

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

Similar topics

4
by: James Goodman | last post by:
I have an asp page which the user enters a time(e.g. 12:21). I need to insert this time into a SQL Server DB, which has a smalldatetime column. I have tried everything I can think of (such as...
2
by: Lauren Quantrell | last post by:
In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004 instead of 1/31/2004 10:30:25 AM How can I do this in a stored procedure? lq
1
by: Joey Martin | last post by:
How do I convert an nvarchar field to smalldatetime format? My nvarcharfield (saledatetext) is in the format YYYY-MM-DD. I want to convert is to smalldatetime (field: saledate) Thanks for...
7
by: Marc Pelletier | last post by:
Hello, I have a table with a Day field, defined as smalldatetime. I am filling it from a CSharp application with the following code: DataRow r = dtStaDays.NewRow(); r= station_ID; r =...
3
by: sunshinevaldes | last post by:
Hello all and thank you for your time. I have a datagrid where I want to view dates (smalldatetime) and be able to sort on the field. However, I do not want to have the time part show. If I...
7
by: Bostonasian | last post by:
I have a table that contains transactional data. Such as site view by whom, when, which template, etc, etc... Everytime when I pulled the report, hh:mm:ss never matters. Only breakdown by dates,...
5
by: Dimitri Furman | last post by:
This looks like a bug - hopefully somebody can explain what is actually happening. Using SQL Server 2000 SP4. Here's a repro script with comments: /* repro table */ CREATE TABLE dbo.T ( ID...
6
by: SQL Server | last post by:
I've been working this for a while. Kind of new to SQL Server functions and not seeing what I am doing wrong. I have this function CREATE FUNCTION dbo.test (@Group varchar(50)) RETURNS...
2
by: sallyme | last post by:
Hi I am passing Date value form .asp form like "3/15/2007" And i am geting error Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) Syntax error converting character string to...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.