Post reply

date problem with SQL 2000

  • May 23, 2003 at 3:07 am

    #458312

    Beware that the date/time format is dependant on several things. NT/2000, version of IIS, whether a user is logged in or not, whether users are impersonated, the system default locale. To name but a few, any of these could affect the date/time format.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • May 23, 2003 at 12:37 pm

    #458407

    Agree with a5xo3z1. Had a similar problem with older ASP app which was brought back from archives. Resolution was rather then to let the users 'type' a date, use a pop calendar in vbscript which interprete date according to client(local) settings but force date to 'dd MMM YYYY' when passing to SQL backend. Seems to fine for a intranet environment.

  • May 26, 2003 at 12:39 am

    #458463

    We struggled some month with an application used in international environments. The problems are caused by date queries.

    SQL 2000 interprets dates depending on the language of the server, the language of logins and the regional windows settings. The best way to get rid of this is to send date queries in the following format:

    { d 'yyyy-mm-dd'} for example: { d '2003-05-24' }

    This proofs to work in all environments.

    Dieter

  • May 26, 2003 at 12:55 am

    #458465

    Hallo Dieter,

    kaum zu glauben, ein weiterer deutscher SQL User. Hurra!!!

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • May 26, 2003 at 2:09 pm

    #458548

    dk-fr, do your environment have , let say a german SQL server installation and a italian/french client for example?

    If you say yyyy-mm-dd what is the 'SET DATEFORMAT' setting on the server?

  • May 28, 2003 at 5:14 am

    #458797

    quote:

    dk-fr, do your environment have , let say a german SQL server installation and a italian/french client for example?

    If you say yyyy-mm-dd what is the 'SET DATEFORMAT' setting on the server?

    SET DATEFORMAT ymd

  • May 28, 2003 at 5:32 am

    #458801

    quote:

    Its about the Date() and Now() function in ASP Programming. Which date format will these functions adopt?

    There is a nice way of not struggeling when creating your AdHoc queries in ASP/VB. Use the nice function FORMAT. Ex:

    Format(Now(), "YYYY-MM-DD HH:MM:SS")

    As this format is a format that always works with SQL server.

    Regards, Hans!

  • May 28, 2003 at 6:51 pm

    #458938

    Hanslindgren, what if default dateformat on server is 'yyyy-dd-mm'? Most unlikely scenario!

    Suppose an Italian setting client connecting to a German SQL Server will also run into trouble where the three character months are used.

    Example Eng->Oct and German->Okt.

    Agree your suggestion seems to be the better so far.

  • May 29, 2003 at 1:50 am

    #458954

    After some tests I happily note that I have learned something new and alot of old code can go in the trash or get some nice modifications 😉

    Thanx to 5409045121009 for showing me the way out of Darkness and to Antarer686 for showing me into the Light 🙂

    Regards, Hans!

  • May 30, 2003 at 1:20 am

    #459152

    Thanks for your replies. Yes, I agree SET DATE FORMAT will work as well. But you need to know the date format your client application is sending to SQL.

    We had these date problems with client applications sending dates in the current Windows date format without any formatting. If you use i.E. the Access Datepart function to create this ANSI date format you don't have to take care about it. These date formatting functions correspond to the regional settings of Windows clients.

    And for me (sorry I'm German) the American date format looks as strange as { d 'yyyy-mm-dd'}.

    Hallo Frank, sind hier wirklich so wenige Deutsche vertreten ? Danke für deinen Gruß. Man tut sich nicht immer leicht im Kreis der Gurus.

    Dieter

  • June 11, 2003 at 10:33 am

    #460814

    Change the query like this

    SELECT * FROM table WHERE startdate > '14-Apr-2003'

    or

    SELECT * FROM table WHERE startdate > 'Apr 14 2003'

    this will work

    regards

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply