Conversion failed when converting date and or time from character string in SQL SERVER 2008

Question

I have below SQL    UPDATE  student queues  SET  Deleted 0          last accessed by  raja          last accessed on CONVERT VARCHAR 24   23-07-2014 09 37 00  113   WHERE std id IN   2144-384-11564       AND reject details  REJECT    when I ran the above SQL the below exception has been throwed   Conversion failed when converting date and or time from character string

User · Answer

DECLARE  FromDate DATETIME  SET  FromDate     Jan 10 2016 12 00AM   DECLARE  ToDate DATETIME SET  ToDate    Jan 10 2017 12 00AM   DECLARE  Dynamic Qry nvarchar Max       SET  Dynamic Qry  SELECT   CONVERT DATETIME  SELECT       CASE WHEN      IssueDate        IssueDate    THEN                 EMP DOCUMENT ISSUE DATE            WHEN    IssueDate      ExpiryDate     THEN                       EMP DOCUMENT EXPIRY DATE ELSE EMP DOCUMENT APPROVED ON END              CHEKDATE    101      FROM CR EMP DOCUMENT  as EMP DOCUMENT WHERE 1 1   AND          CONVERT DATETIME  SELECT          CASE WHEN      IssueDate        IssueDate    THEN                  EMP DOCUMENT ISSUE DATE               WHEN    IssueDate      ExpiryDate     THEN EMP DOCUMENT EXPIRY DATE               ELSE EMP DOCUMENT APPROVED ON END               CHEKDATE    101      BETWEEN       CONVERT CHAR 10    FromDate  126        AND     CONVERT CHAR 10     ToDate   126             print  Dynamic Qry  EXEC  Dynamic Qry

User · Answer

If you re trying to insert in to last accessed on  which is a DateTime2  then your issue is with the fact that you are converting it to a varchar in a format that SQL doesn t understand   If you modify your code to this  it should work  note the format of your date has been changed to  YYYY-MM-DD hh mm ss   UPDATE  student queues  SET  Deleted 0        last accessed by  raja         last accessed on CONVERT datetime2  2014-07-23 09 37 00   WHERE std id IN   2144-384-11564   AND reject details  REJECT    Or if you want to use CAST  replace with   CAST  2014-07-23 09 37 00 000  AS datetime2    This is using the SQL ISO Date Format

User · Answer

Seems like last accessed on  is a date time  and you are converting  23-07-2014 09 37 00  to a varchar  This would not work  and give you conversion errors  Try   last accessed on  convert datetime  23-07-2014 09 37 00   103      I think you can avoid the cast though  and update with  23-07-2014 09 37 00   It should work given that the format is correct   Your query is not going to work because in last accessed on  which is DateTime2 type   you are trying to pass a Varchar value   You query would be  UPDATE  student queues SET  Deleted 0    last accessed by  raja   last accessed on convert datetime  23-07-2014 09 37 00   103     WHERE std id IN   2144-384-11564   AND reject details  REJECT

[sql-server] Conversion failed when converting date and/or time from character string in SQL SERVER 2008

Examples related to sql-server

Examples related to sql-server-2008-r2

Examples related to date-conversion