Datetime field and SQL statement condition (between, ....)
-
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?
Thank to everybody....any suggestion will be appreciated
-
"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."
Maybe that's a typo but 01/01/2005 is earlier than 02/06/2005 so there are no rows that meet the condition. That is there are no rows where the date is >= 02/06/2005 AND <= 01/01/2005.
-
What's wrong with the date format you see in the query analyzer?
What format would you like to see?
-
Sorry,
I tried :
select * from table where DateTest between 02/06/2005 and 01/01/2005
but no result in query analyzer....
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
I think it is a problem with datatype conversion from query datetime datatype and date format in table....
Can anyone to help me..........
-
More informations...
in query analyzer if I print gatedate():
print getdate()
the result is:
2005-06-02 10:48:56.623
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 query: select * from table where otherfieldname = 'somevalue'
the result in DateTest field is:
2005-05-13 01:13:14.717
The format from query analyzer and datatime table are different.........
thank for any help..................
-
To make life easier when dealing with dates, try not to use ambigious dateformats.
select * from table where DateTest between 02/06/2005 and 01/01/2005
There is no way to read these dates without risk of getting it wrong. I have no clue whether 02/06/2005 is 6th of february or 2nd of june. Nor does SQL Server, and that's where your problem starts. Whenever you use ambigous dateformats you leave yourself open to interpetations of the defaulot formats of the tools you are using. This is very messy. And on top of that, the datetime datatype doesn't store datetimes in anything that even resembles a date that we humans can read anyway. So, it's all about display and parsing.
Say you want dates from january 1 up to and including may 31st. You do not want june 1st to be included.
I recommend that you don't use between, though it's possible, it's harder to read beacuse it requires that the reader knows how between works in order to gauge if the parameters are correct.
Instead, write it like this:
SELECT column
FROM table
WHERE dateColumn >= '20050101'
AND dateColumn < '20050601'
The ISO format above ssyymmdd is the only dateformat that is not dependant on language or region settings.
For more info about dates subject, you can read more here. http://www.karaszi.com/SQLServer/info_datetime.asp
/Kenneth
-
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
-
It's not a good idea letting SQL to implicit convert strings to datetime... i always prefer to force an explicit conversion, so you don't need to take care on how the data looks like... try using CONVERT( datetime, '13/05/2005', 103).
-
erncelen
No, your problem was not the date format. As ron K pointed out, the problem is the order of dates in your "between" clause. Earliest date first, latest date last. If you reverse the order it will not return any rows.
(Basically "between" resolves to ">= firstValue and <= LastValue").
So,
"between 02/06/2005 and 01/01/2005" does not work, as February comes after January. But...
"between 01/01/2005 and 02/06/2005" works, for the same reason.
Signature is NULL
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply