Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Inserting date? driving me nuts

    Hi all.
    I'm coding in classic asp and using ms sql server.
    i have a form in my site admin, where im trying to add a promotion with an expiry date.
    My 'live' DB Table uses a date format of 'datetime', but when i try to insert a record through the form, i get this error.
    Code:
    "Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query."
    It works fine on my local developing pc. I've checked the Database tables on both local and live servers and they're exactly the same.

    The code i'm using to insert the form data is:
    Code:
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 135, 1, -1, MM_IIF(Request.Form("expirydate"), Request.Form("expirydate"), null)) ' adDBTimeStamp
    Yep it's dreamweaver insert code.

    What does the error mean when it says 'use the CONVERT function' ?

    Many Thanks
    Andy

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Where I live, dates are written in dd/mm/yyyy format. Simply CASTing a date string to a date or datetime data type does not work for me. I would get wrong results and errors.
    Code:
    select CAST('11/12/1999' as DATE) -- 1999-11-12 WRONG, it should have been 1999-12-11
    select CAST('13/12/1999' as DATE) -- Msg 241, Level 16, State 1
    	--Conversion failed when converting date and/or time from character string.
    I need to supply a special code to SQL Server to tell it how it should interpret the string. There is no way to pass that special code to CAST, but you can with CONVERT:
    Code:
    select CONVERT(DATE, '13/12/1999', 103) -- 1999-12-13
    This is a good source for al those date and datetime codes.
    It works fine on my local developing pc. I've checked the Database tables on both local and live servers and they're exactly the same.
    You should check the locale (country) settings on your PC and the server.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    Hi Wim
    I'm in the UK and also use the same date format (dd/mm/yyyy).
    I'm still not clear on how to Convert the data from my form text field!
    I force the user to enter the date in the correct format (dd/mm/yyyy), but i'm unsure about how to format this line of code.

    Code:
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 135, 1, -1, MM_IIF(Request.Form("expirydate"), Request.Form("expirydate"), null)) ' adDBTimeStamp
    Thanks
    Andy

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Sorry, I'm not an ASP programmer. Can't help you there.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You appear to be trying to force a date value into a TIMESTAMP column. As far as I know, that won't work, anywhere or anyhow.

    Review Data Types (Transact-SQL) for some insight.

    I'm pretty sure that the ADO parameter type 133 should be used in place of 135.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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