March 8, 2011 at 8:57 am
I'm using SQL Server 2008. I have a table called "Notes" with the field "Description" that is VARCHAR(255).
This [Description] field contains all sorts of data, but I need to parse out some dates. Sample data:
[font="Courier New"]Delivered on Mar 15 2010 12:53PM
abc123
Delivered on May 20 2010 3:23PM
This is not at the office.[/font]
My query:
SELECT
CAST(RIGHT(Description,19) AS DATETIME) AS 'ParsedDateTime'
FROM Notes
WHERE
ISDATE(RIGHT(Description,19)) = 1
Output:
[font="Courier New"]2010-03-15 12:53:00.000
2010-05-20 15:23:00.000[/font]
However, if I then try to conditionally pull out certain dates against this CAST VARCHAR, I get an error:
SELECT
CAST(RIGHT(Description,19) AS DATETIME) AS 'ParsedDateTime'
FROM Notes
WHERE
ISDATE(RIGHT(Description,19)) = 1
AND CAST(RIGHT(Description,19) AS DATETIME) = '2010-03-15'
Error received:
[font="Courier New"]Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.[/font]
I've tried changing both CASTs to CONVERTs and get similar errors. Any thoughts would be appreciated. TIA.
March 8, 2011 at 8:59 am
Unfortunately, the Where clause doesn't necessarily evaluate sequentially, so it doesn't work when you do that.
The thing to do is first insert all the ones Where IsDate = 1 into a temp table, then select the specific rows you want from that. Break it down into two pieces.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 8, 2011 at 9:06 am
Thanks, GSquared. I'll go give that a try and will let you know whether or not it worked.
March 8, 2011 at 9:10 am
GSquared beat me to explaining the way the where clause evaluates, but I think you have another logic bomb at the moment.
2010-03-15 12:53:00.000 is not equal to 2010-03-15 00:00:00.000
So unless you are actually expecting no results from your modified query, you still won't get the match I think you're looking for.
If you are on a version of SQL that supports the DATE data type, you can cast as DATE rather than as DATETIME.
-Ki
-Ki
March 8, 2011 at 10:57 am
GSquared: Got it working per your suggestion. Thanks!
Kiara: I realized that after I posted it. I changed my code to use DATE instead of DATETIME as I didn't care about the time information anyway. Thanks!
March 9, 2011 at 6:47 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply