Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Smile Unanswered: insert date as dd/mm/yy format

    Hi all,

    I have an application that receive date input from user in the dd/mm/yyyy format. When I tried to insert the record into the SQL Server with an INSERT statement, I received an error message indicating the date index is out of range. I guess, the SQL Server is expecting the date format to be mm/dd/yyyy. Is there any way that I can input the date into SQL Server in dd/mm/yyyy format.

    Thank You
    hawwa

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, use SET DATEFORMAT.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    number one (back me up rdjabarov) is to change the app.

    number two could be to use the convert statement in your insert clause, to convert the date
    however i am intrigued by pats solution as i have not used this statement before and will try it

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Pat, this WILL NOT HELP, and Scott, - you betchya, the app needs to be changed (not much, just this stubbornly annoying insistance on entering the format that is different from the one that corresponds to the initial server locale settings)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Nov 2004
    Location
    Malaysia
    Posts
    9
    i don't know whether i can help or not... but just try...
    what i know is to change the date to dd/mm/yyyy format, can use:
    convert(varchar(10), getdate(), 103)

    sorry if this cannot help anything

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for the jolly factor, give this a shot:
    Code:
    CREATE TABLE dbo.tDateOrder(
       d		DATETIME		NOT NULL
       )
    GO
    
    SET DATEFORMAT dmy
    GO
    INSERT tDateOrder (d) VALUES ('31/1/04')
    GO
    INSERT tDateOrder (d) VALUES ('2/28/04')
    GO
    
    SET DATEFORMAT mdy
    GO
    INSERT tDateOrder (d) VALUES ('31/3/04')
    GO
    INSERT tDateOrder (d) VALUES ('4/30/04')
    GO
    
    SELECT * FROM tDateOrder
    -PatP

  7. #7
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95

    Post

    personally I'd be careful converting from US to UK date formats

    Here is a link so that maybe everyone can begin to understand the problem is NOT formatting

    http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=189

Posting Permissions

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