| Author |
 Topic  |
|
|
erncelen
Starting Member
Italy
15 Posts |
 Posted - 06/01/2005 : 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
USA
35017 Posts |
Posted - 06/01/2005 : 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 |
Edited by - tkizer on 06/01/2005 19:36:20 |
 |
|
|
vivek.kumargupta
Starting Member
India
45 Posts |
Posted - 06/02/2005 : 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
India
22469 Posts |
Posted - 06/02/2005 : 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 |
Edited by - madhivanan on 06/02/2005 01:40:46 |
|
|
|
erncelen
Starting Member
Italy
15 Posts |
Posted - 06/02/2005 : 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
India
22469 Posts |
Posted - 06/02/2005 : 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
Italy
15 Posts |
Posted - 06/02/2005 : 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
USA
35017 Posts |
Posted - 06/02/2005 : 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 - 06/02/2005 : 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
USA
35017 Posts |
Posted - 06/02/2005 : 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
India
2 Posts |
Posted - 03/23/2012 : 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
153 Posts |
Posted - 03/23/2012 : 10:42:04
|
| I like it; resurrecting a 7 year old thread to post the wrong answer. |
|
|
| |
Topic |
|
Help: Date in field table and Select condition
