Professional Web Applications Themes

Date Time? - Microsoft SQL / MS SQL Server

What is the best way to use dates for comparison? I have the dates stored as datetime types in SQL, and need to do a select via stored proc mp_GetData (instartDate as datetime, inEndDate as datetime) as select * from table where somedatefield between instartDate and inEndDate 'doesnt seem to work Thanks KT...

  1. #1

    Default Date Time?

    What is the best way to use dates for comparison? I have the dates stored as
    datetime types in SQL, and need to do a select via stored proc
    mp_GetData (instartDate as datetime, inEndDate as datetime) as
    select * from table where somedatefield between instartDate and inEndDate
    'doesnt seem to work

    Thanks KT


    Guest

  2. #2

    Default Re: Date Time?

    "Doesn't seem to work" isn't a very descriptive clue as to what may be
    wrong. Are you getting an error or just the wrong results? The way you
    describe the select should be fine.

    --

    Andrew J. Kelly
    SQL Server MVP


    <KT> wrote in message news:phx.gbl... 
    as 
    inEndDate 


    Andrew Guest

  3. #3

    Default Re: Date Time?

    I agree as long as the data type is typed a datetime type (datetime,
    smalldatetime) it will follow the logical order of string matching for date
    data. However, if you for instance used varchar to represent the data it may
    not work exactly as planned since alphabetic order is different from how
    datetime is ordered and it would be a string compare not a numeric compare
    (remember dates are stored as two part integers part for date, part for
    time). What exactly thou is the problem with the resultset.

    Also a common mistake is the last date value of the between. It is common
    for users to assume 2/12/2003 should include 2/12/2003 1:00 PM but keep in
    mind a date value without a time will default as 0 time or 12:00 AM so you
    between will miss data for the day if you wanted to include as well. Usually
    rolling the date forward 1 day will be fine but keep in mind it will pick
    anything up for the next day that has a time part of 12:00 AM, so just be
    sure you fix like you need.


    "Ray Higdon" <com> wrote in message
    news:phx.gbl... 


    James Guest

  4. #4

    Default Re: Date Time?

    > I agree as long as the data type is typed a datetime type (datetime, 
    date 
    may 

    Unless you used an ISO safe date format, e.g. YYYYMMDD will order the same
    on a character basis as a (small)datetime would on a datetime basis.


    Aaron Guest

  5. #5

    Default Re: Date Time?

    Although the hit is a small in most all cases it helps in a high transaction
    environment to avoid as much IO on the Hard Drive as possible.

    Even when using ISO safe date format you should explicilty convert the date
    values for you between range to datetime datatype.

    Also, looking at the execution plan under SQL 2000 I see that SQL chooses to
    do a implicit convert from the ISO date to datetime format thus it means 1
    read extra each run (CONVERT reads the format from syslanguages to make the
    conversion).

    Whereas CAST even against the ISO format uses the com object for this which
    makes no hit against the HD IO.

    Both have a minor memory IO hit and cpu cycles.

    Another factor and reason for being explicit is between versions of SQL that
    stand now and those that may come the implicit conversion choices have
    changed. What worked under SQL 7 does not always perform the same under
    2000. By being explicit my code can roll forward with much thought unless
    they change keywords or datatype names which is far slimer of a chance.


    "Aaron Bertrand [MVP]" <com> wrote in message
    news:phx.gbl... 
    > date 
    > may [/ref]
    compare 


    James Guest

Similar Threads

  1. CFMX7.0.1 Administrator date time issue showing 13hrsbehind server time
    By bayfield in forum Coldfusion Server Administration
    Replies: 0
    Last Post: December 8th, 04:34 AM
  2. Replies: 3
    Last Post: October 21st, 07:13 PM
  3. Time/Date format and changing time to GMT
    By Shaiboy_UK in forum ASP
    Replies: 1
    Last Post: October 19th, 01:04 PM
  4. Date and time
    By CINDY in forum Microsoft Access
    Replies: 2
    Last Post: August 4th, 05:02 PM
  5. Date and time...
    By SamMan in forum Macromedia Dreamweaver
    Replies: 12
    Last Post: July 20th, 11:58 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139