date format problem

Question

Post reply

date format problem

nageswara.dhaveji

SSChasing Mays

Points: 601
More actions
January 4, 2009 at 10:23 pm

#97117

Hi Happy New year to All,
I had a problem while working on date format. See the below 2 queries:
1:-
select * from acts a inner join leads b on a.id = g.id
where b.Showroom_id =8 and
a.Created_date between '12/29/2008 00:00:00.000'
and '01/02/2009 23:59:59.000'
2:-
select * from acts a inner join leads b on a.id = b.id
where b.Showroom_id =8
and convert(varchar(10),a.Created_date,101)
between '12/29/2008' and '01/02/2009'
For 1st query it is giving results. But, for 2nd query it is not giving any result. If I use first query, thru application it is taking more time and giving error 'Thread aborted'. To avoid the error I tried with second query. But, no result is displayed. Can anybody help me?
Dates ('12/29/2008' and '01/02/2009') are passed from application.
Nagesh

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

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

nageswara.dhaveji SSChasing Mays Points: 601 More actions · Answer 1

Hi,

I found the solution...

When I used cast(convert(varchar(10),a.Created_date,101) as datetime) in second query it is working...

thanks,

Nagesh

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Query #1's problems are probably a case of bad indexing, but are almost certainly solvable in any event. Please provide the execution plan and the table & index definitions.

Query #2 is flawed in several ways and is really not worth salvaging.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

brainy SSCrazy Points: 2445 More actions · Answer 3

For 1st query it is giving results. But, for 2nd query it is not giving any result. If I use first query, thru application it is taking more time and giving error 'Thread aborted'. To avoid the error I tried with second query. But, no result is displayed. Can anybody help me?
Dates ('12/29/2008' and '01/02/2009') are passed from application.

I dont feel... the second query increase the performance... since it might not use the index if created on that column.

I suggest you to use first query only.

ramu.valleti SSCrazy Points: 2229 More actions · Answer 4

You can simply try without using cast and convert...

Eg: WHERE a.Created_date BETWEEN '01/04/2007' AND '01/06/2007'

Regards,

Ramu

Ramu
No Dream Is Too Big....!

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

My very strong recommendation would be that both queries are wrong simply because of the use of BETWEEN. In the first query, you miss out on a whole second of the day and, in the second, you destroy the possibility of an Index Seek.

Because of the possible time element in any datetime query, you should write these types of queries as follows...

select * from acts a inner join leads b on a.id = g.id

where b.Showroom_id =8 and

a.Created_date >= @StartDate

and a.Created_date < @EndDate + 1

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

Sony Antony SSC-Addicted Points: 452 More actions · Answer 6

As far as I know, convert is not necessary if you use date in the following format

yyyy-mm-dd hh:mm:ss.mls

Try giving date in this format

2008-12-29 00:00:00.000

I Think this will solve your date problem. I have not checked with your joins anyway.

Regards,

Sony Antony,

Manager - Projects,

Max IT Services.

http://www.maxits.net

smantony@maxits.net