Msg 241, Level 16, State 1, Line 25 Conversion failed when converting date and/or time from character string.

Question

Msg 241, Level 16, State 1, Line 25 Conversion failed when converting date and/or time from character string.

Analyst_SQL 3,576

Error Getting
Msg 241, Level 16, State 1, Line 25
Conversion failed when converting date and/or time from character string.

When I am mentioned 'A'
,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME)) THEN 'A' ELSE [IN(A_Date)] END [IN(A_Date)]

  Create table #EmpMaster (EnrollNumber int, empName varchar(50))
            Create table #MonthDate (Day varchar(50),D_Date date)
        Create table #AttendenceLOG (EnrollNumber int,A_Date datetime)
           Insert into #EmpMaster values (10001,'ALi')
        Insert into #EmpMaster values (10002,'Kami')
        Insert into #EmpMaster values (10003,'Jhon')
        Insert into #EmpMaster values (10004,'Muli') 
          Insert into #EmpMaster values (10005,'Rose') 

             Insert into #MonthDate values ('Sunday','2021-06-06') 
                 Insert into #MonthDate values ('Monday','2021-06-07') 
                    Insert into #MonthDate values ('Tuesday','2021-06-08') 
                       Insert into #MonthDate values ('Wednesday','2021-06-09') 


         Insert into #AttendenceLOG values (10001,'2021-06-09 08:04:00')
          Insert into #AttendenceLOG values (10001,'2021-06-09 17:45:00')
              Insert into #AttendenceLOG values (10002,'2021-06-09 10:04:00')
             Insert into #AttendenceLOG values (10003,'2021-06-09 20:04:00')
          Insert into #AttendenceLOG values (10004,'2021-06-09 07:45:00')
            Insert into #AttendenceLOG values (10004,'2021-06-09 09:45:00')
               Insert into #AttendenceLOG values (10004,'2021-06-09 20:35:00')


;with cte as (
 select a.EnrollNumber,b.D_Date,b.Day,c.A_Date 
 from #EmpMaster a 
 cross join #MonthDate b
 left join #AttendenceLOG c 
 on a.EnrollNumber=c.EnrollNumber 
 and cast(c.A_Date as date)=b.D_Date)
 ,cte1 AS (
 SELECT  EnrollNumbeR,D_Date,Day
 ,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]
 FROM CTE 
 GROUP BY EnrollNumbeR,D_Date,Day)
 ,CTE2 AS (
 SELECT EnrollNumbeR,D_Date,Day
 ,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME)) THEN 'A' ELSE [IN(A_Date)] END [IN(A_Date)]
 ,CASE WHEN [OUT(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([OUT(A_Date)] AS DATE) AS DATETIME)) THEN [OUT(A_Date)] ELSE NULL END [OUT(A_Date)]
 FROM cte1)
 SELECT *
 ,CASE WHEN [DAY] ='Sunday' THEN 'S'
       WHEN [IN(A_Date)] IS NULL AND [OUT(A_Date)] IS NULL THEN 'A'
     WHEN DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8 THEN 'P'
     ELSE 'A' END STATUS,
 CASE WHEN [IN(A_Date)] IS NOT NULL AND [OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8 
 THEN CAST(CAST(DATEDIFF(MINUTE,[IN(A_Date)],[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR]
 FROM CTE2
 ORDER BY D_Date,EnrollNumbeR

Accepted answer

3 additional answers

Your answer

Answer 1

Hi @Analyst_SQL ,

Please also refer below:

 ;with cte as (  
 select a.EnrollNumber,b.D_Date,b.Day,c.A_Date   
 from #EmpMaster a   
 cross join #MonthDate b  
 left join #AttendenceLOG c   
 on a.EnrollNumber=c.EnrollNumber   
 and cast(c.A_Date as date)=b.D_Date)  
 ,cte1 AS (  
 SELECT  EnrollNumbeR,D_Date,Day  
 ,MIN(A_Date) [IN(A_Date)],MAX(A_Date) [OUT(A_Date)]  
 FROM CTE   
 GROUP BY EnrollNumbeR,D_Date,Day)  
 ,CTE2 AS (  
 SELECT EnrollNumbeR,D_Date,Day  
 ,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME)) THEN NULL ELSE [IN(A_Date)] END [IN(A_Date)]  
 ,CASE WHEN [OUT(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([OUT(A_Date)] AS DATE) AS DATETIME)) THEN [OUT(A_Date)] ELSE NULL END [OUT(A_Date)]  
 FROM cte1)  
 ,CET3 as (  
 SELECT *  
 ,CASE WHEN [DAY] ='Sunday' THEN 'S'  
       WHEN [IN(A_Date)] IS NULL AND [OUT(A_Date)] IS NULL THEN 'A'  
     WHEN DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8 THEN 'P'  
     ELSE 'A' END STATUS,  
 CASE WHEN [IN(A_Date)] IS NOT NULL AND [OUT(A_Date)] IS NOT NULL AND DATEDIFF(HOUR,[IN(A_Date)],[OUT(A_Date)])>=8   
 THEN CAST(CAST(DATEDIFF(MINUTE,[IN(A_Date)],[OUT(A_Date)]) AS FLOAT)/60 AS decimal(10,2)) ELSE 0 END [HOUR]  
 FROM CTE2)  
 SELECT EnrollNumbeR,D_Date,Day  
 ,CASE WHEN [IN(A_Date)] IS NULL AND [OUT(A_Date)] IS NULL AND [STATUS]='A'   
 THEN [STATUS]   
 ELSE CONVERT(varchar(30),[IN(A_Date)],121)   
 END [IN(A_Date)]  
 ,[OUT(A_Date)],[STATUS],[HOUR]  
 FROM CET3  
  ORDER BY D_Date,EnrollNumbeR

Output:

If above is not working, please provide the rule of [IN(A_Date)] ='A'. Thanks.

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

MelissaMa-MSFT 24,221 Reputation points

2021-07-06T01:20:21.227+00:00

Hi @Analyst_SQL ,

Could you please validate and provide any update?

If it still not working, please provide rule of [IN(A_Date)] ='A' or more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa

Answer 2

Naomi Nosonovsky 8,431

You can not have 'A' and an actual date as a result. You need to have some date placeholder for the first case or otherwise convert to character your actual date value.

Analyst_SQL 3,576 Reputation points

2021-07-03T03:08:40.753+00:00

how to convert it ,i want to mentioned 'A" when there is not IN Time
Analyst_SQL 3,576 Reputation points

2021-07-03T06:10:26.343+00:00

I want below output.
tibor_karaszi@hotmail.com 4,316 Reputation points

2021-07-03T15:20:37.05+00:00

In that case, the returned column cannot be datetime, it has to be a string. I.e., in that CASE, convert the datetime column to a string.
Analyst_SQL 3,576 Reputation points

2021-07-03T15:34:55.92+00:00

@tibor_karaszi@hotmail.com ,

I am not inserting into table 'A' ,i am just applying condition.or can you put example here
tibor_karaszi@hotmail.com 4,316 Reputation points

2021-07-03T16:20:05.357+00:00

Look at below expression:

CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME)) THEN 'A' ELSE [IN(A_Date)] END

Look at the finishing part:

THEN 'A' ELSE [IN(A_Date)] END

The column IN(_ADate) is a datetime column, but the 'A' is a string. Datetime has higher precendence than a string, so SQL Server tries to convert 'A' to datetime, and that fails. You should convert IN(_ADate) to a string.
Analyst_SQL 3,576 Reputation points

2021-07-03T16:50:54.387+00:00
i tried below,but issue still persist.

WHEN (Convert(varchar(50),[IN(A_Date)] )) is null then 'A'
Naomi Nosonovsky 8,431 Reputation points

2021-07-04T22:35:14.62+00:00

Change this
,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME)) THEN 'A' ELSE [IN(A_Date)] END [IN(A_Date)]

into

,CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME)) THEN 'A' ELSE convert(varchar(30),[IN(A_Date)],121) END [IN(A_Date)]

In other words, you need to convert your (IN(A_DATE)) column into character

Answer 3

tibor_karaszi@hotmail.com 4,316

You are converting the expression you check against NULL. That is not what I was referring to. You need to convert the output:

CASE WHEN [IN(A_Date)] >=DATEADD(HOUR,12,CAST(CAST([IN(A_Date)] AS DATE) AS DATETIME)) THEN 'A' ELSE CONVERT([IN(A_Date)] AS varchar(40)) END

Now both results are strings: 'A' and CONVERT([IN(A_Date)] AS varchar(40)).

Analyst_SQL 3,576

These error below are coming.

Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'TimeIN'.

Naomi Nosonovsky 8,431 Reputation points

2021-07-04T22:36:41.283+00:00

Tibor meant CAST function but accidentally wrote CONVERT. Check https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15 and select appropriate code. I think 121 is what you're after.

Answer 4

Stacy Clark 31

Default value should be converted to the data type of column by query executor.

Date data type requires data in " YYYY-mm-dd" fromat.

tibor_karaszi@hotmail.com 4,316 Reputation points

2021-07-06T10:31:22.79+00:00

"Default value should be converted to the data type of column by query executor."

No, it is the rules called "data type precedence" that determines which side is converted. In version 7.0 and earlier, it was converted to the column side, but in 2000 it changes to respect the "Data type precedence" rules.

"Date data type requires data in " YYYY-mm-dd" fromat."

SQL Server accepts many format, some of the are language dependent in how they interpret what is month and what is date. The above mentioned format is not language dependent (i,.e., it is "unsafe") when converted to the datetime and smalldatetime types (it id language neutral for date, datetime2 and datetimeoffset, though). More on this here: https://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

Share via

Msg 241, Level 16, State 1, Line 25 Conversion failed when converting date and/or time from character string.

3 additional answers

Your answer