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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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



Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -