Query where dd/mm/yyyy - 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

Old Forums

CLOSED - General SQL Server

Query where dd/mm/yyyy

Author

Topic

darrengan
Starting Member

5 Posts

Posted - 2005-06-29 : 06:40:32

Hi friends,

I am having problem quering date (dd/mm/yyyy)and it always show wrong data.

this is the scripts:
SELECT date FROM tbl WHERE date BETWEEN '28/06/2005' AND '29/06/2005'

How do i solve this problem? i have searched everywhere and there is no answer.

Thanks!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-06-29 : 06:52:35

What are you expecting and what are you getting?

Mark

darrengan
Starting Member

5 Posts

Posted - 2005-06-29 : 06:56:23

Hie mwjdavidson,

In my table, all the dates are stored in dd/mm/yyyy.

When i want to query a range of date, say from 20/06/2005 to 29/06/2005, i use the scripts below:

SELECT date FROM tbl WHERE date BETWEEN '20/06/2005' AND '29/06/2005'

but it will not query the correct date.

do i need to convert or something?

Cheers!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-29 : 07:19:14

Try this
SELECT date FROM tbl WHERE date >'20-jun-2005' AND date <'30-jun-2005'
Did you use DateTime or SmallDateTime datatype?
Refer this also
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50545

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-29 : 07:31:59

or simply use
SELECT date FROM tbl WHERE date BETWEEN '20050620' AND '20050629'
and have no other problems with date conversions.

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

darrengan
Starting Member

5 Posts

Posted - 2005-06-29 : 07:38:36

Hie friends....

Thanks for the reply...

In my table, the date (dd/mm/yyyy) is stored as text (sucks huh)

If i change to datetime format(in table), all the dates will jumble up with mm/dd/yyyy

is there anything sql can do without changing the date format or data type in the table?

all the suggested query does not work

Cheers & thanks

madhivanan
Premature Yak Congratulator

22864 Posts

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

quote:
In my table, the date (dd/mm/yyyy) is stored as text (sucks huh)

Did you mean Varchar type?
If so, it should work


Declare @t table(d varchar(20))
insert into @t values('10/02/2005')
insert into @t values('11/02/2005')
insert into @t values('12/02/2005')
insert into @t values('13/02/2005')
insert into @t values('14/02/2005')
insert into @t values('15/02/2005')
insert into @t values('16/02/2005')
select d from @t where d between '10/02/2005' and '14/02/2005'

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-29 : 08:09:40

set dateformat dmy
SELECT date FROM tbl WHERE convert(datetime, date) BETWEEN '20050620' AND '20050629'

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

darrengan
Starting Member

5 Posts

Posted - 2005-06-29 : 12:34:43

Hie folks..

i didnt mention that i am using ms access.

i have change the datatype for the date to Date/Time and it appears as dd/mm/yyyy in the table.

i have found the script to query:

SELECT date FROM tbl WHERE sr_date BETWEEN #20/06/2005# and #29/06/2005#

it manage to query correctly according to the date range but the result format shows '2005-06-20 00:00:00'. I need to show it in 'dd/mm/yyyy'

I have tried:

SELECT convert(datetime,date,203) FROM tbl WHERE sr_date BETWEEN #20/06/2005# and #29/06/2005#

but it seems that ms access does not support 'convert'

any solution

Hope that this post assist others.

Cheers!

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-29 : 14:58:13

quote:
Originally posted by darrengan

Hie folks..

i didnt mention that i am using ms access.

There is an access forum elsewhere on SQLTeam...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-30 : 00:43:12

quote:
i didnt mention that i am using ms access.

You can use format function


SELECT format(date,"dd/mm/yyyy") FROM tbl WHERE sr_date BETWEEN #20/06/2005# and #29/06/2005#

Otherwise you can get the dates as they are and format them into your presentation layer

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources