Dates Problem... - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dates Problem...

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-07-20 : 05:32:41

Hi Everyone.

I am using the between getting the dates, i m facing some problem with the same.

CreatedDate
2005/07/19
2005/07/19
2005/07/20

this is the sample date which there in the table.

select * from TabName where CreatedDate Between '2005/07/18' And '2005/07/20'

when i pass the query is gives following output

CreatedDate
2005/07/19
2005/07/19

the record '2005/07/20' is not comming ..

i Analysed the same, and found the problem is because of the time..i suppose..

then i passed the following query... which worked fine for me..

Select * From TabName Where Covert(Varchar,CreatedDate,111) Between
Convert(Varchar,'2005/07/18' ,111) and Convert(Varchar,'2005/07/20' 111)

this returned me all the records..

i want to know is this only solution are there is some other way to do it.. ? and would like to know is this because of time thing are something other...

thanks

Complicated things can be done by simple thinking

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-20 : 06:24:26

you can get rid of the time portion with this:
SELECT GetDate(), DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)

Select * From TabName
Where CreatedDate Between
DATEADD(d, DATEDIFF(d, 0, '2005/07/18'), 0)
and DATEADD(d, DATEDIFF(d, 0, '2005/07/20')+1, 0) -- notice the +1 day. that gives all dates on 20th.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-20 : 06:52:29

It is bacause the date has time portion and you write a query without including that
So you have to use what spirit1 specified or simply as

select * from TabName where CreatedDate >='2005/07/18' And 
CreatedDate < DateAdd(d,1,'2005/07/20')

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-07-20 : 07:45:56

Thanks a lot...

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources