Professional Web Applications Themes

Date comparisons - Microsoft SQL / MS SQL Server

Hi. I'm using ADO.NET to retrieve data from SQL Server via stored procedures. Lately I found the following problem: There's a stored procedure that has a DateTime input parameter (say statusDate). This parameter is passed from ADO.NET code as DateTime. Inside stored procedure, there's a comparison of dates that looks like "...WHERE createDate = statusDate". Here, "createDate" is a column of datetime type. The application was deployed on number of different servers and worked just fine. Except one specific server where this comparison fails, although the dates seem equal. I suspect that this might have something to do with Locale/Regional ...

  1. #1

    Default Date comparisons

    Hi. I'm using ADO.NET to retrieve data from SQL Server via stored
    procedures. Lately I found the following problem:

    There's a stored procedure that has a DateTime input parameter (say
    statusDate). This parameter is passed from ADO.NET code as DateTime.
    Inside stored procedure, there's a comparison of dates that looks like
    "...WHERE createDate = statusDate". Here, "createDate" is a column of
    datetime type. The application was deployed on number of different
    servers and worked just fine. Except one specific server where this
    comparison fails, although the dates seem equal. I suspect that this
    might have something to do with Locale/Regional Settings, although I
    don't really see how they can impact in this case. Maybe someone can
    give me a hint. Thanks.
    --
    Direct access to this group with [url]http://web2news.com[/url]
    [url]http://web2news.com/?microsoft.public.sqlserver.programming[/url]
    melo Guest

  2. #2

    Default Re: Date comparisons

    I think you guessed it right. This must be problem with the server's
    language settings. By default SQL server can understand format yyyymmdd.
    hence while passing value make sure statusdate variable's value matches
    that format.

    --
    -Vishal
    "melo" <melo.news.invalidweb2news.net> wrote in message
    news:49099N829web2news.com...
    > Hi. I'm using ADO.NET to retrieve data from SQL Server via stored
    > procedures. Lately I found the following problem:
    >
    > There's a stored procedure that has a DateTime input parameter (say
    > statusDate). This parameter is passed from ADO.NET code as DateTime.
    > Inside stored procedure, there's a comparison of dates that looks like
    > "...WHERE createDate = statusDate". Here, "createDate" is a column of
    > datetime type. The application was deployed on number of different
    > servers and worked just fine. Except one specific server where this
    > comparison fails, although the dates seem equal. I suspect that this
    > might have something to do with Locale/Regional Settings, although I
    > don't really see how they can impact in this case. Maybe someone can
    > give me a hint. Thanks.
    > --
    > Direct access to this group with [url]http://web2news.com[/url]
    > [url]http://web2news.com/?microsoft.public.sqlserver.programming[/url]

    Vishal Parkar Guest

  3. #3

    Default Re: Date comparisons

    I suggest that you run a profiler trace and compare a working with the non-working SQL Server.
    My guess is that the client application passes the date in a format which depends on regional
    setting instead of a "safe" format.

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "melo" <melo.news.invalidweb2news.net> wrote in message news:49099N829web2news.com...
    > Hi. I'm using ADO.NET to retrieve data from SQL Server via stored
    > procedures. Lately I found the following problem:
    >
    > There's a stored procedure that has a DateTime input parameter (say
    > statusDate). This parameter is passed from ADO.NET code as DateTime.
    > Inside stored procedure, there's a comparison of dates that looks like
    > "...WHERE createDate = statusDate". Here, "createDate" is a column of
    > datetime type. The application was deployed on number of different
    > servers and worked just fine. Except one specific server where this
    > comparison fails, although the dates seem equal. I suspect that this
    > might have something to do with Locale/Regional Settings, although I
    > don't really see how they can impact in this case. Maybe someone can
    > give me a hint. Thanks.
    > --
    > Direct access to this group with [url]http://web2news.com[/url]
    > [url]http://web2news.com/?microsoft.public.sqlserver.programming[/url]

    Tibor Karaszi Guest

  4. #4

    Default Re: Date comparisons

    The date conversion "failure" could be in the .Net code since within SQL
    Server a date is a date is a double.

    If the date settings in the control panel are (EG) d/m/y on the troublesome
    machine, but m/d/y on all others this could happen when converting Text in
    the UI to Date prior to putting it into the parameter value.

    Using Profiler will help as then you will be able to see the parameter
    values passed into SQL Server.

    - Tim


    "melo" <melo.news.invalidweb2news.net> wrote in message
    news:49099N829web2news.com...
    > Hi. I'm using ADO.NET to retrieve data from SQL Server via stored
    > procedures. Lately I found the following problem:
    >
    > There's a stored procedure that has a DateTime input parameter (say
    > statusDate). This parameter is passed from ADO.NET code as DateTime.
    > Inside stored procedure, there's a comparison of dates that looks like
    > "...WHERE createDate = statusDate". Here, "createDate" is a column of
    > datetime type. The application was deployed on number of different
    > servers and worked just fine. Except one specific server where this
    > comparison fails, although the dates seem equal. I suspect that this
    > might have something to do with Locale/Regional Settings, although I
    > don't really see how they can impact in this case. Maybe someone can
    > give me a hint. Thanks.
    > --
    > Direct access to this group with [url]http://web2news.com[/url]
    > [url]http://web2news.com/?microsoft.public.sqlserver.programming[/url]

    Tim Guest

Similar Threads

  1. Date comparisons using UNIX timestamp?
    By sue in forum PHP Development
    Replies: 4
    Last Post: February 17th, 10:45 PM
  2. comparisons and depth
    By chicken king webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: February 4th, 02:33 AM
  3. #26080 [NEW]: Comparisons always true in xml.c
    By AxelLuttgens at swing dot be in forum PHP Development
    Replies: 0
    Last Post: November 2nd, 03:01 PM
  4. chaining comparisons
    By Kurt M. Dresner in forum Ruby
    Replies: 28
    Last Post: July 22nd, 05:03 AM
  5. NASA G5 v PC speed comparisons
    By Pique in forum Mac Applications & Software
    Replies: 2
    Last Post: July 9th, 05:59 PM

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