Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
calpolyjeff
Starting Member
3 Posts |
 Posted - 2010-01-07 : 14:02:14
|
| I'm working on a SQL SELECT statement that extracts 6 numbers from a larger string, and then converts those numbers to a date. The numbers are actually supposed to be a date, but I suspect that there have been data entry errors that result in the numbers including nc=valid characters, such as letters, or a numerical month that is higher than 12, say. (I suspect this because when I run th SQL statement with the conversion, sometimes it works great, and sometimes it completely crashes with an error message about bad syntax wghen converting the string to a date.)So is ther some kind of test I can pur into, say, a CASE WHEN statment that will first test to see that I have valid data before I try to convert it and crash?ADP/SQL database system.Thanx |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-07 : 14:09:48
|
| You can use isDate(stringValueHere) |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
calpolyjeff
Starting Member
3 Posts |
Posted - 2010-01-07 : 14:44:34
|
| THanks for your responses. The problem seems to be with converting from the string to the date. I think I need to test the string *before* I convert to the date. Here's the code I use to get the date:CAST(SUBSTRING(datastring, 7, 2) + SUBSTRING(datastring, 3, 4) AS smalldatetime)As you can see, I have to rearrange the string to get the 6 numbers in the right order for a date. This conversion usually works, but the crashes I suspect are due to an invalid string trying to be converted to a date. (And it's a bad crash when it crashes!) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-08 : 02:39:23
|
| how is your string format currently? does it crash even after you put filter where isdate(field)= 1 and len(field) >=8? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-08 : 02:39:24
|
quote:
Originally posted by calpolyjeff
THanks for your responses. The problem seems to be with converting from the string to the date. I think I need to test the string *before* I convert to the date. Here's the code I use to get the date:CAST(SUBSTRING(datastring, 7, 2) + SUBSTRING(datastring, 3, 4) AS smalldatetime)As you can see, I have to rearrange the string to get the 6 numbers in the right order for a date. This conversion usually works, but the crashes I suspect are due to an invalid string trying to be converted to a date. (And it's a bad crash when it crashes!)
Can you post some sample data with expected result?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Testing for format before converting