Post reply

i have a date problem

  • June 9, 2004 at 4:35 am

    #161602

    iam inserting some records

    actually iam using ASP for An entry screen, where the user types details and the press save,

    iam using database SQL-SERVER 2000, and iam using Ado 2.7

    iam from india and our data format is dd/mm/yyyy

    but when the user types the data and save it automatically is changed

    like for example iam typing 09/06/2004

    in this 09 is the day 06 is the month and 2004 is the year

    but month and day seems to take each other positions, previously there was no such problem all of sudden it started to occur, now what do i do,

    any solution for this.

    pls suggest

    thanks in Advance

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • June 9, 2004 at 4:51 am

    #509607

    1. Use dd MMM yyyy format when saving data.

    2. After creating the connection, run

    SET DateFormat dmy

     

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • June 9, 2004 at 5:19 am

    #509610

    where exactly to set ,?? iam not getting u

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • June 9, 2004 at 5:28 am

    #509611

    before running your statements, run this statement:SET DateFormat dmy

    remember; this statement is connection wide.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • June 9, 2004 at 5:29 am

    #509612

    is there any proper solution for this problem,

    becoz in most of the pages iam using insert queries

    and iam inserting with sql server format

    yyyy-mm-dd

    now we are using asp pages and our website is uploaded into another server, will there change in setting will create problem for us, which is the best way to resolve

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • June 9, 2004 at 5:36 am

    #509613

    where exactly and what exactly to type

    sql="SET DateFormat dmy"

    con.execute sql

    ?

    is it ?

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • June 9, 2004 at 5:38 am

    #509614

    yes.

     

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • June 9, 2004 at 5:42 am

    #509615

    you talk very less,

    just enough to understand

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • June 9, 2004 at 5:47 am

    #509616

    thank you very much for your valuable comments. I am sorry, I should have elobrated it in the first place itself.  However, if you have any issues, please feel free to convey

    (Hope you wont take this remark as "very less" )

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • June 9, 2004 at 5:57 am

    #509619

    Check the Default Language on the logins. It affects date format.

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

  • June 9, 2004 at 7:34 am

    #509634

    Sukhoi,

    SET DATEFORMAT allows you to tell SQL Server what format the date is being sent as.

    You can use several different formats:

    SET DATEFORMAT dmy

    SET DATEFORMAT mdy

    SET DATEFORMAT ymd

    So, for your input mm/dd/yyyy, you would use SET DATEFORMAT mdy at the beginning of your script. Now I don't use ADO, but this is what it would look like in a SQL Server script.

    SET DATEFORMAT mdy

    INSERT INTO mytable (mydate, lname, fname)

    VALUES ('09/06/2004', 'Fleming', 'Bill')

    Hope that helps explain it better. Refer to the BOL, use the Index tab and enter SET DATEFORMAT for more information.

    -SQLBill

  • June 9, 2004 at 8:03 am

    #509645

    There is only one date format that will cause you no trouble, no grief, no nothing wherever in the world you'll go.

    This is the standard ISO format ssyymmdd (eg 20040609)

    In SQL Server its convert style 112.

    As already noted, mm/dd/yyyy and dd/mm/yyyy is no good, because this is ambigous formats relying on other information outside the date itself in order to decode it correctly (ie what dateformat was/is active etc..)

    Similarly, dd mmm yyyy is not good either, as it's sensitive to the language settings.

    So, the solution is that the client should submit the date to SQL Server as a ssyymmdd string. This will ensure that the date will be stored correct regardless of dateformat settings etc.

    (this does not necessarily mean that the client must display this format, it's just what the code submitting the data should use when the record is saved)

    /Kenneth

Viewing 12 posts - 1 through 11 (of 11 total)

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