Post reply

What the difference between char and datetime in this situation?

  • April 21, 2007 at 7:12 am

    #195420

    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

    #701837

    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.

    LinkedIn Profile

  • April 21, 2007 at 3:33 pm

    #701847

    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

    #701878

    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

    #701882

    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