SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Help: Date in field table and Select condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

erncelen
Starting Member

Italy
15 Posts

Posted - 06/01/2005 :  19:24:34  Show Profile  Reply with Quote
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

USA
37142 Posts

Posted - 06/01/2005 :  19:35:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 06/01/2005 19:36:20
Go to Top of Page

vivek.kumargupta
Starting Member

India
45 Posts

Posted - 06/02/2005 :  00:06:23  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 06/02/2005 :  01:37:05  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 06/02/2005 01:40:46
Go to Top of Page

erncelen
Starting Member

Italy
15 Posts

Posted - 06/02/2005 :  04:46:36  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 06/02/2005 :  05:22:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Italy
15 Posts

Posted - 06/02/2005 :  12:24:07  Show Profile  Reply with Quote
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

USA
37142 Posts

Posted - 06/02/2005 :  12:30:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 06/02/2005 :  12:44:54  Show Profile  Reply with Quote
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

USA
37142 Posts

Posted - 06/02/2005 :  12:47:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

India
2 Posts

Posted - 03/23/2012 :  09:37:11  Show Profile  Reply with Quote
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 - 03/23/2012 :  10:42:04  Show Profile  Reply with Quote
I like it; resurrecting a 7 year old thread to post the wrong answer.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000