Results 1 to 4 of 4

Thread: Datetime Query

  1. #1
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144

    Unanswered: Datetime Query

    i am trying to query a datetime column in a db.

    e.g. 3/7/2005 4:24:01 AM
    My query is below :-
    --
    select a.date, b.useruri as 'FROM', c.useruri as 'TO',
    a.body as 'MESSAGE' from messages as a
    inner join
    users as b
    on a.fromid = b.userid
    inner join
    users as c
    on a.toid = c.userid
    where a.date like '%2005-03-01%'
    order by a.date

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Specify the times using BETWEEN. Otherwise, you won't use any indexes and this will be extremely slow.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144
    Do i specify the date as a i wrote in the query. since the datetime is like
    3/7/2005 4:24:01 AM ?

    or do i have to declare the datime if it was today and use the variable in the query.

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Well, it depends what you're trying to achieve. If the data was stored like that, then just go from 00:00:00 to 23:59:59. If it was stored with more accuracy, it can get a little tricky. Note the following code results followed by an excert from Books Online:

    CODE:
    Code:
    DECLARE @dates TABLE(date1 DATETIME)
    
    INSERT @dates(date1)
    	SELECT '01/01/05 13:58:01.000' UNION ALL
    	SELECT '01/02/05 00:00:00.000' UNION ALL
    	SELECT '01/02/05 00:00:00.001' UNION ALL
    	SELECT '01/02/05 23:59:59.999' UNION ALL
    	SELECT '01/03/05 00:00:00.000' UNION ALL
    	SELECT '01/03/05 00:00:00.001' UNION ALL
    	SELECT '01/04/05 10:00:00.001' 
    
    SELECT date1 FROM @dates
    
    SELECT date1
    FROM @dates
    WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.999'
    
    
    SELECT date1
    FROM @dates
    WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.997'
    BOL Quote:

    Date and time data types for representing date and time of day.

    datetime

    Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

    Example Rounded example
    01/01/98 23:59:59.999 1998-01-02 00:00:00.000
    01/01/98 23:59:59.995,
    01/01/98 23:59:59.996,
    01/01/98 23:59:59.997, or
    01/01/98 23:59:59.998 1998-01-01 23:59:59.997
    01/01/98 23:59:59.992,
    01/01/98 23:59:59.993,
    01/01/98 23:59:59.994 1998-01-01 23:59:59.993
    01/01/98 23:59:59.990 or
    01/01/98 23:59:59.991 1998-01-01 23:59:59.990


    Microsoft® SQL Server™ rejects all values it cannot recognize as dates between 1753 and 9999.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •