[Solved] Conversion failed when converting date and/or time from character string in SQL Server

Last Reply one month ago By dharmendr

Posted one month ago

when i am executing below query then error is coming 

Conversion failed when converting date and/or time from character string.

Entrydate datatype is date

ALTER Procedure [dbo].[Sp_Rags_ContOpeningViewAll]
    @date date =getdate
as 
begin

    SELECT C.CID, C.ContNo as Ref_NO, C.ConWeight as Weight,
        Convert(varchar,C.entrydate,103) as EntryDate, C.No_Of_Bales AS QTY,
        C.Contrackno as Container_No
    FROM Containerno AS C INNER JOIN
         ConCatagory AS D ON C.Cat_ID = D.Cat_ID
         inner join C_Agent on C_Agent.A_ID=C.A_ID
         inner join Forwarder on Forwarder.F_ID=C.F_ID
    where Convert(varchar,C.entrydate,103)  between @date and @date
    order by C.CID desc

Sample data

CREATE TABLE #Containerno (CID INT,RID int,DID int,Entrydate date ) 
 
insert into  #Containerno Values(1,101,1,'2019-07-01')
insert into  #Containerno  Values(2,101,1,'2019-07-02')
insert into  #Containerno Values(3,101,1,'2019-07-03')
insert into  #Containerno Values(4,101,1,'2019-07-04')
insert into  #Containerno Values(5,101,1,'2020-02-12')
insert into  #Containerno Values(6,101,1,'2020-02-12')
insert into  #Containerno Values(7,101,1,'2020-02-12')

 

You are viewing reply posted by: dharmendr one month ago.
Posted one month ago

Hi akhter,

The error Conversion failed when converting date and/or time from character string is due to below possible reason.

Date accept YYYY-MM-dd format.

Some record might be exceeded the Month and Date value.

i.e. Month value greater than 12 or Date value greater that max value of the specified month

Example :

2020-13-12 - Month value is 13.

2020-02-30 - Date is 30 which doesn't belongs to Month 02 (Feb)

So please verify the posibilities.

Rest query is correct.

SQL

CREATE TABLE #Containerno (CID INT,RID INT,DID INT,Entrydate DATE) 
INSERT INTO #Containerno VALUES(1,101,1,'2019-07-01')
INSERT INTO #Containerno VALUES(2,101,1,'2019-07-02')
INSERT INTO #Containerno VALUES(3,101,1,'2019-07-03')
INSERT INTO #Containerno VALUES(4,101,1,'2019-07-04')
INSERT INTO #Containerno VALUES(5,101,1,'2020-02-12')
INSERT INTO #Containerno VALUES(6,101,1,'2020-02-12')
INSERT INTO #Containerno VALUES(7,101,1,'2020-02-12')

SELECT CID,RID,DID,CONVERT(VARCHAR,Entrydate,103) AS EntryDate
FROM #Containerno

DROP TABLE #Containerno

Output

CID RID  DID  EntryDate

1    101    1    01/07/2019

2    101    1    02/07/2019

3    101    1    03/07/2019

4    101    1    04/07/2019

5    101    1    12/02/2020

6    101    1    12/02/2020

7    101    1    12/02/2020