Help: Date in field table and Select condition

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)

Help: Date in field table and Select condition

Author

Topic

erncelen
Starting Member

15 Posts

Posted - 2005-06-01 : 19:24:34

A big problem.......

A table has got a field -named datetest - data type is datetime

in Enterprise manager - open table and I see in open table the field dateTest records are shown like:
13/05/2005 1.13.15

If I execute a query in query analyzer, the field result is shown as:
2005-05-13 01:13:14.717

When I try to execute a query like:

select * from table where DateTest between 02/06/2005 and 01/01/2005 i do not find anything (no error recorded), but I am sure there are record with dates within the interval written above.

My questio is:

How to write the right format date in query analyzer?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 19:35:43

Well there aren't any rows between 02/06/2005 and 01/01/2005. Your dates are backwards. Your query needs to be like this:

select * from table where DateTest between '01/01/2005' and '02/06/2005'

For more information, check out BETWEEN in SQL Server Books Online.

Tara

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-02 : 00:06:23

Modify the query as follows
select * from table where DateTest between '01/01/2005'and '02/06/2005'
You will get the correct result

Thanks,
Vivek

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 01:37:05

quote:
Originally posted by vivek.kumargupta

Modify the query as follows
select * from table where DateTest between '01/01/2005'and '02/06/2005'
You will get the correct result

Thanks,
Vivek

Vivek, can you tell us how yours is different from Tara's suggesstion
erncelen, you can use dd-mmm-yyyy format also

select * from table where DateTest between
'01-jan-2005' and '06-Feb-2005'

Madhivanan

Failing to plan is Planning to fail

erncelen
Starting Member

15 Posts

Posted - 2005-06-02 : 04:46:36

I tried:
select * from table where DateTest between '01/01/2005'and '02/06/2005' but the query doesn't produce any result,

then I tried :select * from table where DateTest = '13/05/2005'
(I know there is a record dated: 13/05/2005 1.13.15)
but the result is:
The conversion of a char data type to a datatime data type resulted in out-of-range datetime value

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 05:22:42

As you used Datetime datatype, you have to check as
select * from table where DateTest between '01-Jan-2005'and '06-Feb-2005'
or
select * from table where DateTest > '13-May-2005' and DateTest < '14-May-2005'

Madhivanan

Failing to plan is Planning to fail

erncelen
Starting Member

15 Posts

Posted - 2005-06-02 : 12:24:07

Thank to everybody,

I have found out the problem.....

In the sql statement I have to write: mm/dd/yyyy

select * from table where DateTest between 01/01/2005 and 02/06/2005,

and everything works

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 12:30:30

Isn't that what I suggested 11 minutes after you posted the initial question?

You might want to look into SET DATEFORMAT in SQL Server Books Online as well.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-02 : 12:44:54

quote:
Originally posted by tduggan

Isn't that what I suggested 11 minutes after you posted the initial question?

You might want to look into SET DATEFORMAT in SQL Server Books Online as well.

Tara

What?

You expect them to read the post as well?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 12:47:56

What I love the most is this:

quote:

select * from table where DateTest between '01/01/2005'and '02/06/2005' but the query doesn't produce any result

And now the next day, it's working.

Tara

sarathy
Starting Member

2 Posts

Posted - 2012-03-23 : 09:37:11

Hi
Try this query you get correct result

select * from Table where [Datetimefield] between convert(varchar,[Datetimefield])and convert(varchar,getdate() )

S.Partahasarthy

uberman
Posting Yak Master

159 Posts

Posted - 2012-03-23 : 10:42:04

I like it; resurrecting a 7 year old thread to post the wrong answer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources