Problem with comaparing exact date in WHERE part of query....

Question

Problem with comaparing exact date in WHERE part of query....

m12k_102

SSC Rookie

Points: 33
More actions
December 17, 2009 at 6:24 pm

#213812

Hi...
I got a problem with comparing date on WHERE part of my query even the date is in exact format...
Table Name : cash_up
Field: date_out
date_out value: '2009-12-17 10:45:52.5900000'
Note: CAST('2009-12-17 10:45:52.5900000' as datetime) = '12/17/2009 10:45:52 AM'
My Query:
SELECT
CAST(date_out as datetime) as date_out
FROM cash_up
WHERE
CAST(date_out as datetime) = '12/17/2009 10:45:52 AM'
I got no result.....
somebody know how can i fix this.... please ...
many thanks for any reply.....

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

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

Please read the Best practice article below and post some test data, since that provides us to see what error you are getting. It enables us to test a readily available code instead of we building it from scratch.

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

It makes the responses come quicker.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

What data type is date_out? Can you post some sample data from it?

BTW, running that function on your column in the WHERE clause will prevent indexes from being used.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ray K SSC-Dedicated Points: 31163 More actions · Answer 3

I just tried running your query on one of my own tables that has dates, and didn't have a problem.

BTW, Grant is correct . . . you should avoid including functions (such as CAST) in your WHERE clause.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I can't even get the value, '2009-12-17 10:45:52.5900000', to CAST to datetime, I get this error:

Msg 241, Level 16, State 1, Line 12
Conversion failed when converting date and/or time from character string.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

Jack Corbett (12/18/2009)
I can't even get the value, '2009-12-17 10:45:52.5900000', to CAST to datetime, I get this error:
Msg 241, Level 16, State 1, Line 12
Conversion failed when converting date and/or time from character string.

Should probably work with new DATETIME2 datatype, Jack.

--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)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Jeff,

The OP specifically casts to datetime in the code provided so that's what I ran.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question