Announcement

Collapse
No announcement yet.

Access forces US Date format when using #dd/mm/yyyy#

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Access forces US Date format when using #dd/mm/yyyy#

    Hi all

    After going mad on Friday trying to stop my sql statement (in an ASP page) using American style date format even when everything associated with the code, the server, the database etc... was set to UK.

    Then i found this:

    http://allenbrowne.com/ser-36.html (see point 2)

    How odd! Has anyone else seen this or is there an easy way round it? I ended up hacking the date into day month year and reassembling it as month day year. 7 lines of code as a function.
    Server 2000 MCP
    Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

    ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

  • #2
    Re: Access forces US Date format when using #dd/mm/yyyy#

    try using yyyy-mm-dd in the query.
    How is it stored in the DB?
    "...if I turn out to be particularly clear, you've probably misunderstood what I've said” - Alan Greenspan

    Comment


    • #3
      Re: Access forces US Date format when using #dd/mm/yyyy#

      It is stored in UK format (dd/mm/yyyy). I dont see how reversing it will make a difference.

      I ended up doing this:

      Code:
      sday = left(QrySDate,2)
      smonth = mid(QrySDate, 4, 2)
      syear = right(QrySDate, 4)
      
      QrySDate = smonth & "/" & sday & "/" & syear
      Then QrySDate is popped into the query. It now works like this.
      Server 2000 MCP
      Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

      ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

      Comment


      • #4
        Re: Access forces US Date format when using #dd/mm/yyyy#

        Its not reversing it

        The a true date field is not actually stored in the way that is presented to you, however most DBMS 's can translate the yyyy-mm-dd format to the date that in the DB, provided that it really is a date/time field.
        "...if I turn out to be particularly clear, you've probably misunderstood what I've said” - Alan Greenspan

        Comment


        • #5
          Re: Access forces US Date format when using #dd/mm/yyyy#

          Originally posted by Lior_S View Post
          Its not reversing it

          The a true date field is not actually stored in the way that is presented to you, however most DBMS 's can translate the yyyy-mm-dd format to the date that in the DB, provided that it really is a date/time field.
          Ok well my fix seems to have sorted it. If it comes up again ill try your suggestion.
          Server 2000 MCP
          Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

          ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

          Comment


          • #6
            Re: Access forces US Date format when using #dd/mm/yyyy#

            Since you're talking ASP (and not Access), here's the solution:

            1) Set session.LCID regardless of server regionalization

            2) Use CDate("literal") vs/ #literal#

            Here's my test ASP (ran same on total English UK or USA:

            Code:
            <%@Language=VBSCRIPT%>
            <% Option Explicit %>
            
            <html>
            <header>
            <title>Regional Settings Date Test</title>
            </header>
            
            <body>
            <% 
            Dim dt
            
            session.LCID = 2057	'English(British) format
            'response.LCID = 2057	'English(British) format
            %>
            
            <%
            dt = #12/03/07#
            %>
            
            <p><%=dt%></p>
            
            <% 
            dt = Date
            %>
            
            <p><%=dt%></p>
            
            <%
            dt = CDate("12/03/07")
            %>
            
            <p><%=dt%></p>
            </body>
            </html>
            Here's the output (on server or worstation):

            Code:
            03/12/2007
            
            12/03/2007
            
            12/03/2007
            So even thougn #literal# is always USA format (I was surprised), CDate does work as expected.
            Cheers,

            Rick

            ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

            © 2006-2099 R Valstar. This post is offered "as is" for discussion purposes only with no express or implied warranty of any kind including, but not limited to, correctness or fitness for use. Nothing herein shall be construed as advice. Attempting any activity based on information in this post is done at your own risk.

            Comment


            • #7
              Re: Access forces US Date format when using #dd/mm/yyyy#

              Originally posted by rvalstar View Post
              Since you're talking ASP (and not Access), here's the solution:

              1) Set session.LCID regardless of server regionalization
              Already tried that. It does not make a blind bit of difference in the case of a SQL statement probing an Access Database.

              Try doing this on an access database using ASP:

              Code:
              SELECT * FROM bookings WHERE BookingStartDate > #01/03/2007# AND BookingStartDate < #10/03/2007#
              What I get is everything between 03/01/2007 and 03/10/2007.
              Server 2000 MCP
              Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

              ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

              Comment


              • #8
                Re: Access forces US Date format when using #dd/mm/yyyy#

                More evidence that there is no way around it:

                http://www.fontstuff.com/access/acctut15.htm (see section Get the Date Format Right)

                http://groups.google.com/group/micro...06970e5c22fcea

                http://groups.google.com/group/micro...48a01b366ba0e0

                http://www.adit.co.uk/html/sql_for_access.htm (see section dates)

                Hard to believe but you need to use US date formats when using SQL statements with Access.
                Server 2000 MCP
                Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

                ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                Comment


                • #9
                  Re: Access forces US Date format when using #dd/mm/yyyy#

                  2) Use CDate("literal") vs/ #literal#

                  EDIT: and try FormatDateTime to get the dates back out in correct format.
                  Last edited by rvalstar; 12th March 2007, 21:33.
                  Cheers,

                  Rick

                  ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                  © 2006-2099 R Valstar. This post is offered "as is" for discussion purposes only with no express or implied warranty of any kind including, but not limited to, correctness or fitness for use. Nothing herein shall be construed as advice. Attempting any activity based on information in this post is done at your own risk.

                  Comment


                  • #10
                    Re: Access forces US Date format when using #dd/mm/yyyy#

                    I'm obviously missing something here...

                    Can you give an example of using CDATE and then passing it into my SQL statement?
                    Server 2000 MCP
                    Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

                    ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                    Comment


                    • #11
                      Re: Access forces US Date format when using #dd/mm/yyyy#

                      Originally posted by tonyyeb View Post
                      I'm obviously missing something here...

                      Can you give an example of using CDATE and then passing it into my SQL statement?
                      Or I am.

                      Back to your example:

                      Code:
                      SELECT * FROM bookings WHERE BookingStartDate > #01/03/2007# AND BookingStartDate < #10/03/2007#
                      I take it you really mean the following in practice based on the earlier example:

                      Code:
                      "SELECT * FROM bookings WHERE BookingStartDate > #" & _
                      QrySDate & "# AND BookingStartDate < #" & QryEDate & "#
                      No doubt the #literal# in USA format is whack but we can deal with that.

                      My contention is that once you have these strings stored internally as dates in the ASP, life becomes so much easier (no substring crappauge). I think it also good to convert to date first just to verify the value is a valid date.

                      What I'm suggesting is you convert the strings to dates under the British Locale ID then use long dates via FormatDateTime OR convert to the USA Locale ID and use general / short dates. Long dates don't work well for USA since it puts the day of the week in front.

                      Here's my ASP:

                      Code:
                      <%@Language=VBSCRIPT%>
                      <% Option Explicit %>
                      
                      <html>
                      <header>
                      <title>Regional Settings Date Test</title>
                      </header>
                      
                      <body>
                      
                      <% 
                      Dim QrySDate, QryEDate, dtQrySDate, dtQryEDate
                      
                      QrySDate = "01/03/2007"
                      QryEDate = "10/03/2007"
                      
                      Response.Write("<p>Using strings (BAD): ""SELECT * FROM bookings WHERE BookingStartDate > #" & _
                      	QrySDate & "# AND BookingStartDate < #" & QryEDate & "#""</p>")
                      
                      session.LCID = 2057	'English(British) format
                      
                      dtQrySDate = CDate(QrySDate)
                      dtQryEDate = CDate(QryEDate)
                      
                      Response.Write("<p>British Format</p>")
                      
                      Response.Write("<p>Using general dates (BAD): ""SELECT * FROM bookings WHERE BookingStartDate > #" & _
                      	dtQrySDate & "# AND BookingStartDate < #" & dtQryEDate & "#""</p>")
                      
                      Response.Write("<p>Using long dates (GOOD): ""SELECT * FROM bookings WHERE BookingStartDate > #" & _
                      	FormatDateTime(dtQrySDate, 1) & "# AND BookingStartDate < #" & FormatDateTime(dtQryEDate, 1) & "#""</p>")
                      
                      session.LCID = 1033	'English(United States) format
                      
                      Response.Write("<p>United States Format</p>")
                      
                      Response.Write("<p>Using general dates (GOOD): ""SELECT * FROM bookings WHERE BookingStartDate > #" & _
                      	dtQrySDate & "# AND BookingStartDate < #" & dtQryEDate & "#""</p>")
                      
                      Response.Write("<p>Using long dates (BAD): ""SELECT * FROM bookings WHERE BookingStartDate > #" & _
                      	FormatDateTime(dtQrySDate, 1) & "# AND BookingStartDate < #" & FormatDateTime(dtQryEDate, 1) & "#""</p>")
                      %>
                      
                      </body>
                      </html>
                      Here's the output:

                      Using strings (BAD): "SELECT * FROM bookings WHERE BookingStartDate > #01/03/2007# AND BookingStartDate < #10/03/2007#"

                      British Format

                      Using general dates (BAD): "SELECT * FROM bookings WHERE BookingStartDate > #01/03/2007# AND BookingStartDate < #10/03/2007#"
                      Using long dates (GOOD): "SELECT * FROM bookings WHERE BookingStartDate > #01 March 2007# AND BookingStartDate < #10 March 2007#"

                      United States Format

                      Using general dates (GOOD): "SELECT * FROM bookings WHERE BookingStartDate > #3/1/2007# AND BookingStartDate < #3/10/2007#"
                      Using long dates (BAD): "SELECT * FROM bookings WHERE BookingStartDate > #Thursday, March 01, 2007# AND BookingStartDate < #Saturday, March 10, 2007#"
                      Cheers,

                      Rick

                      ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                      © 2006-2099 R Valstar. This post is offered "as is" for discussion purposes only with no express or implied warranty of any kind including, but not limited to, correctness or fitness for use. Nothing herein shall be construed as advice. Attempting any activity based on information in this post is done at your own risk.

                      Comment


                      • #12
                        Re: Access forces US Date format when using #dd/mm/yyyy#

                        Thanks for your efforts Rick. But i guess my original point of the #date# in a SQL statement being ONLY US format stands. And there seems to be oodles of ways around it, some more elegant than others.
                        Server 2000 MCP
                        Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

                        ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                        Comment


                        • #13
                          Re: Access forces US Date format when using #dd/mm/yyyy#

                          Still, I think Locale ID or FormatDateTime are better than substringing it in ASP code but that's just me.
                          Cheers,

                          Rick

                          ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                          © 2006-2099 R Valstar. This post is offered "as is" for discussion purposes only with no express or implied warranty of any kind including, but not limited to, correctness or fitness for use. Nothing herein shall be construed as advice. Attempting any activity based on information in this post is done at your own risk.

                          Comment


                          • #14
                            Re: Access forces US Date format when using #dd/mm/yyyy#

                            Originally posted by rvalstar View Post
                            Still, I think Locale ID or FormatDateTime are better than substringing it in ASP code but that's just me.
                            Like i said some are more elegant than others... i'm far from an elegant programmer!
                            Server 2000 MCP
                            Development: ASP, ASP.Net, PHP, VB, VB.Net, MySQL, MSSQL - Check out my blog http://tonyyeb.blogspot.com

                            ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                            Comment


                            • #15
                              Re: Access forces US Date format when using #dd/mm/yyyy#

                              For others that come across this thread, here is an alternative to #literal# -- ODBC canonicals:

                              http://snippets.dzone.com/posts/show/756

                              http://msdn.microsoft.com/library/de...p_literals.asp

                              So instead of #mm/dd/yyyy# you would use {d 'yyyy-mm-dd'}

                              Not without substring manipulation but it does eliminate the mm/dd or dd/mm confusion. Don't let the ODBC bit fool you. It works all over the place w/ MS SQL Server and anything that thunks through ODBC.

                              NOW the real thing missing in old fashioned ASP is the limited function set in the underlying VBSCRIPT language -- especially the lack of the traditional VB Format() function. Those of us used to passing dates from VB to Oracle will have something similar to the following:

                              TO_DATE('" & Format(dtVariable, "dd-MMM-yyyy HH:mm:ss") & "', 'DD-MON-YYYY HH24:MI:SS')
                              Several methods to get a Format() into ASP.

                              This one makes an ActiveX component exposing the true VB Format() function:

                              http://www.4guysfromrolla.com/webtech/051601-1.shtml

                              I really like this one attributed to Scott Dixon and packaged by ASP FAQ. This is an old programmer trick from the way back where you use whatever dynamic expression evaluator you have on hand rather than writing your own. I used a similar trick in the late 80's to put formulas into cells on a spreadsheet I wrote on an HP 98xx workstation running Rocky Mountain BASIC and on a port of that spreadsheet to a VAX running Ingres so, as I stated, I'm really partial to this solution:

                              http://classicasp.aspfaq.com/date-ti...es-for-me.html

                              Code:
                              <% 
                                  Function Format(vExpression, sFormat) 
                               
                                      set fmt = CreateObject("MSSTDFMT.StdDataFormat") 
                                      fmt.Format = sFormat 
                               
                                      set rs = CreateObject("ADODB.Recordset") 
                                      rs.Fields.Append "fldExpression", 12 ' adVariant 
                               
                                      rs.Open 
                                      rs.AddNew 
                               
                                      set rs("fldExpression").DataFormat = fmt 
                                      rs("fldExpression").Value = vExpression 
                               
                                      Format = rs("fldExpression").Value 
                               
                                      rs.close: Set rs = Nothing: Set fmt = Nothing 
                               
                                  End Function 
                               
                                  ' sample usage: 
                               
                                  response.write format(now(), "yyyy-mm-dd") 
                                  response.write "<br>" 
                                  response.write format(time(), "hh:mm") 
                              %>
                              The fmt object depends on the availability of MSSTDFMT.dll, which is installed by Visual Studio 6.0. I have not tried to run this script on a machine that did not have Visual Studio 6.0 or later installed, so I'm not sure it will work if you simply copy and/or register the DLL on the target machine.
                              Back in the ASP I did for tonyyeb, I added:

                              Code:
                              Function Format(vExpression, sFormat)
                              	Dim fmt, rs
                              
                              	Set fmt = CreateObject("MSSTDFMT.StdDataFormat") 
                              	fmt.Format = sFormat 
                              
                              	Set rs = CreateObject("ADODB.Recordset") 
                              	rs.Fields.Append "fldExpression", 12 ' adVariant 
                              
                              	rs.Open 
                              	rs.AddNew 
                              
                              	Set rs("fldExpression").DataFormat = fmt 
                              	rs("fldExpression").Value = vExpression 
                              
                              	Format = rs("fldExpression").Value 
                              
                              	rs.close: Set rs = Nothing: Set fmt = Nothing 
                              End Function 
                              
                              ...
                              
                              Response.Write("<p>Using format() and canonical (GOOD): ""SELECT * FROM bookings WHERE BookingStartDate > {d '" & _
                              	Format(dtQrySDate, "yyyy-mm-dd") & "'} AND BookingStartDate < #" & Format(dtQryEDate, "mm/dd/yyyy") & "#""</p>")
                              And I got:

                              Using format() and canonical (GOOD): "SELECT * FROM bookings WHERE BookingStartDate > {d '2007-03-01'} AND BookingStartDate < #03/10/2007#"
                              Notice I used Format() to create the ODBC Canonical for the starting date. Also note, you may need to change the >, < to >=, <= as required to get the date range desired.
                              Cheers,

                              Rick

                              ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

                              © 2006-2099 R Valstar. This post is offered "as is" for discussion purposes only with no express or implied warranty of any kind including, but not limited to, correctness or fitness for use. Nothing herein shall be construed as advice. Attempting any activity based on information in this post is done at your own risk.

                              Comment

                              Working...
                              X