none
Convert date time to string

    Question

  • Hi, i had an error when update datetime, May i know how to convert it ? thank you

    "Microsoft OLE DB Provider for SQL Server error '80040e07',Conversion failed when converting date and/or time from character string"


    UPDATE OTH_INV_PLAN set INV_DATE= '2012-10-30'


    Tuesday, October 30, 2012 6:36 AM

All replies

  • I am not able to replicate your issue. Could you please provide your table structure and the exact complete statement .

    Drop table T1
    Create Table T1(Col1 Date)
    Insert into T1 Select GETDATE()
     Update T1 set Col1 =  '2012-10-30'


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, October 30, 2012 6:41 AM
  • HI, the SQL script i write in ASP code as below

    mySQLUP = "UPDATE OTH_INV_PLAN set INV_DATE= '2012-10-30' "

    '"UPDATE OTH_INV_PLAN set INV_DATE = Convert(datetime,'"&SInvCal&"',112)"

    '"UPDATE OTH_INV_PLAN set INV_DATE='"&SQInvDate&"',INV_PLANQTY = '"&TInvPlanQty&"' where INV_ID = '"&SInvID&"' "

    Set objRSUP = Server.CreateObject("ADODB.Recordset")
    objRSUP.Open mySQLUP, oConnection

    Msg = "Plan details updated !"

    Response.Redirect "plan_adjust.asp?empid="& EmpID & "&Name=" & EmpName & "&EmpLevel=" & EmpLevel  & "&ref=" & Msg

    Tuesday, October 30, 2012 6:48 AM
  • Localization settings may be to blame. To make your code prone to this type of err use explicit MS SQL convertion  http://msdn.microsoft.com/en-us/library/ms187928.aspx


    Serg


    • Edited by SergNL Tuesday, October 30, 2012 6:51 AM
    Tuesday, October 30, 2012 6:51 AM
  • Try the below:

    '"UPDATE OTH_INV_PLAN set INV_DATE = Convert(datetime,Convert(Date,'

    "&SInvCal&"'),112)"


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, October 30, 2012 6:54 AM
  • Hi,

    Microsoft OLE DB Provider for SQL Server error '80040e07' Conversion failed when converting datetime from character string.

    Is the data type of INV_DATE is varchar ? if that then do as below:

    UPDATE OTH_INV_PLAN set INV_DATE =  Cast('2012-10-30' as varchar(11))


    Ahsan Kabir

    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/


    Tuesday, October 30, 2012 7:21 AM
  • YOu should use cast or convert function to convert the data time value in string to datetime.

    FYI. Your syntax should be like this when hitting the DB

    UPDATE OTH_INV_PLAN set INV_DATE= CONVERT(DATETIME ,'2012-10-30',101 )

    For more about datetime cast or convert use the below URL

    http://msdn.microsoft.com/en-us/library/ms187928.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Tuesday, October 30, 2012 7:29 AM
  • Hi,  the data type of INV_DATE is date, after i convert it as date, it  show the result "1900-01-01"

    UPDATE OTH_INV_PLAN set INV_DATE =  Cast('2012-10-30' as date)

    Tuesday, October 30, 2012 7:31 AM
  • Hi,  the data type of INV_DATE is date, after i convert it as date, it  show the result "1900-01-01"

    UPDATE OTH_INV_PLAN set INV_DATE =  Cast('2012-10-30' as date)

    See my below commend

    YOu should use cast or convert function to convert the data time value in string to datetime.

    FYI. Your syntax should be like this when hitting the DB

    UPDATE OTH_INV_PLAN set INV_DATE= CONVERT(DATETIME ,'2012-10-30',101 )

    For more about datetime cast or convert use the below URL

    http://msdn.microsoft.com/en-us/library/ms187928.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, October 30, 2012 7:34 AM
  • Caulson, Try the below:

    Drop table T1 Create Table T1(Col1 Datetime) Insert into T1 Select GETDATE() Select * From t1 Update T1 set Col1 = convert(datetime,'2012-10-30',101) Update T1 set Col1 = convert(datetime,Convert(Date,'2012-10-30'),112)



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, October 30, 2012 7:34 AM
  • HI, i try the convert function, the result is same as "1900-01-01"
    Tuesday, October 30, 2012 7:38 AM
  • What this query returns?

    SELECT  GETDATE() AS Expr1, CONVERT(datetime, '2012-10-30', 101) AS Expr2, CONVERT(datetime, '2012-10-30', 120) AS Expr3, CAST('2012-10-30' AS datetime) AS Expr4


    Serg

    Tuesday, October 30, 2012 7:52 AM
  • Expr 1 2012-10-30 15:59:18.703 

    Expr2 2012-10-30 00:00:00.000

    Expr 3  2012-10-30 00:00:00.000

    Expr 4 2012-10-30 00:00:00.000

    Tuesday, October 30, 2012 8:03 AM
  • Drop table T1
    Create Table T1(Col1 Datetime)
    Insert into T1 Select GETDATE()

    Select Col1 From T1

    And this returns  "1900-01-01", really?

    Serg

    Tuesday, October 30, 2012 8:10 AM
  • Post your complete table structure check for any triggers on that table.

    Check your second update on the same table also..May be &SQInvDate& is an empty string

    UPDATE OTH_INV_PLAN set INV_DATE='"&SQInvDate&"',INV_PLANQTY = '"&TInvPlanQty&"' where INV_ID = '"&SInvID&"' "


    Thanks and regards, Rishabh K


    • Edited by Rishabh K Tuesday, October 30, 2012 8:26 AM
    Tuesday, October 30, 2012 8:23 AM
  • Don't embed your variables into SQL Update command. Always use parameters and this way you're safe from the errors you're experiencing.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, October 30, 2012 3:18 PM