April 21, 2007 at 7:12 am
Suppose I have the table called TableA and the type of check-in date is char:
TableA
pid RoomNo CheckInDate
1 1003 10/10/2007
2 1005 12/10/2007
3 1019 13/10/2007
4 2003 14/10/2007
5 1034 15/10/2007
6 2029 13/11/2007
Query 1:
Select * from TableA where convert(datetime, CheckInDate) between '10/10/2007' and '14/10/2007'
Query 2:
Select * from TableA where CheckInDate >= '10/10/2007' and CheckInDate <='14/10/2007'
Except the same results of these two queries, is there any difference of these.
April 21, 2007 at 8:26 am
The thing with a DATETIME or SMALLDATETIME field is that you have built in data validation for that reason I would prefer to store dates in date fields.
If you are going to store dates as text then stick to the ISO8601 standard which is yyyy-mm-dd hh:mm:ss:mmmmm even if you truncate
it.
DATETIME takes 8 bytes. If you store the date as yyyymmdd then you can use a CHAR(8) field.
The annoying thing about SQL and dates is that it allows different date formats so you have to know whether you are dealing with an American mdy or British dmy date.
Joe Celkos SQL Programming Style recommends using the BETWEEN clause rather than the >= <= construct.
April 21, 2007 at 3:33 pm
Suppose you have the table called TableA and the type of check-in date is char:
(almost like your example, just some dates are changed)
TableA
pid RoomNo CheckInDate
1 1003 10/10/2007
2 1005 12/10/2007
3 1019 13/10/2007
4 2003 14/09/2007
5 1034 15/11/2007
6 2029 1/11/2007
4 2003 1/10/2007
5 1034 25/10/2007
6 2029 23/11/2007
Now try to select records between 1/10/2007 and 1/11/2007.
_____________
Code for TallyGenerator
April 23, 2007 at 1:50 am
Dates is dates !!! Chars are for display only. Trying to process dates as strings is asking for trouble (because of the formatting issues David mentioned) and inefficient. SQL Server has functions (DateAdd and DateDiff) that will process date dates for you but won't work on chars.
April 23, 2007 at 2:22 am
Thanks all guys~~
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply