Basic SQL Query, going wrong somewhere......

Question

Post reply

Basic SQL Query, going wrong somewhere......

JeSuisDicky

SSC Enthusiast

Points: 141
More actions
January 17, 2011 at 3:40 am

#232964

Hi,
I have a table that has a specific column holding dates & also a time e.g. '1/14/2011 16:25:32 PM'.
All I am trying to do is use a basic delete statement implementing like;
DELETE
FROM TASKS
WHERE MODIDATE LIKE '1/14/2011%'
This completes successfully however 0 rows are affected? When there are at least a few thousand.
Also when I query using;
SELECT *
FROM TASKS
WHERE MODIDATE LIKE '1/14/2011%'
It returns 0 rows.
If I use the = clause & specify the exact value of the column it will work fine just not with wildcard which doesn't really help as the time value within each is by the second & as I say there are a few thousand which need removing.
Can anyone shed any light on as to why this might be?
Apologies if I'm doing something daft :ermm:
Thanks
TDB

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

I think this page will help you ....

http://www.karaszi.com/SQLServer/info_datetime.asp

Clear Sky SQL
My Blog[/url]

rpatil SSC Rookie Points: 26 More actions · Answer 2

Try this

Declare @Value char(12)

SET @Value = your char(12) value for 106 format

to get to 106 format

Use this

Select Convert(Char(12),getdate(),106)

This will clarify the date format after that

SET the @Value variable to your desired value and you are there.

IF MODIDATE is not a datetime column then follow this approach

DELETE FROM TASKS

WHERE Convert(Char(12),Convert (Datetime,MODIDATE),106) = @Value

else if its a datetime variable then do this

DELETE FROM TASKS

WHERE Convert (Datetime,Convert(Char(12),MODIDATE,106)) = Convert (Datetime,@Value)

/* Explanation of approach */

The end goal in both the cases is to remove the trailing 24 hour time that gets attached to a date variable and compare on the same format for the date rather than datetime i.e Instead of '2011-01-18 11:18:05.747' I would compare it with '18 Jan 2011 '

run these two and you would get a hold of the path taken

Select getdate()

Select CONVERT(char(12),getdate(),106)

Try for numbers from 101 through 106 till 115

However there is always a space for improvement and it would be great if someone suggests a better path on the same opportunity err problem.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

omd (1/17/2011)
Hi,
I have a table that has a specific column holding dates & also a time e.g. '1/14/2011 16:25:32 PM'.
All I am trying to do is use a basic delete statement implementing like;
DELETE
FROM TASKS
WHERE MODIDATE LIKE '1/14/2011%'
This completes successfully however 0 rows are affected? When there are at least a few thousand.
Also when I query using;
SELECT *
FROM TASKS
WHERE MODIDATE LIKE '1/14/2011%'
It returns 0 rows.
If I use the = clause & specify the exact value of the column it will work fine just not with wildcard which doesn't really help as the time value within each is by the second & as I say there are a few thousand which need removing.
Can anyone shed any light on as to why this might be?
Apologies if I'm doing something daft :ermm:
Thanks
TDB

What is the data-type of the column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

If your column was stored as VARCHAR() what you're listing above should work. However, you don't want dates in that format, so I hope it's not. From your explaination I'd assume it isn't.

What you want to do is find all the records to delete on a given day. There are two ways to approach this. The first is non-SARGable, but easier to read. DATEADD(DATEDIFF()) that you'll see here is merely a way to strip the time component off a datetime.

WHERE DATEADD( dd, DATEDIFF( dd, 0, myColumn), 0) = @DeleteForDate

This will go slow, because each date in the table needs to be recalculated and no index is useful, but will get you your answer.

your better version is this slightly more messy version, but lets your indexes work:

WHERE MyColumn >= @DeleteForDate AND MyColumn < DATEADD( dd, 1, @DeleteForDate)

Please note the >= and the <. You don't want anything for 1/15/2011 when you use the 1/14/2011 @DeleteForDate, but you want everything right up to the millisecond before it.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

@rpatil,

I dont often pull people up , but that is very bad advice.

You ask if there is a better way ,yes there is. Craig mentioned the non-SARGability of such queries.

Read the link i posted previously , I cant stress enough how much i believe that that is REQUIRED reading.

Clear Sky SQL
My Blog[/url]

JeSuisDicky SSC Enthusiast Points: 141 More actions · Answer 6

Dave Ballantyne (1/17/2011)
I think this page will help you ....
http://www.karaszi.com/SQLServer/info_datetime.asp

Thanks for all the replys on the subject, I'd covered the above & got there in the end so many thanks Dave 😉

Cheers

TDB