question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked TiborKaraszi commented

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

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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @akhterhussain-3167,

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:
111671-output.png

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.



output.png (30.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @akhterhussain-3167,

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

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered NaomiNNN commented

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.

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

how to convert it ,i want to mentioned 'A" when there is not IN Time

0 Votes 0 ·

I want below output.

111523-image.png


0 Votes 0 ·
image.png (70.5 KiB)

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.

0 Votes 0 ·
Show more comments
NaomiNNN avatar image NaomiNNN akhterhussain-3167 ·

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

0 Votes 0 ·
TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered NaomiNNN commented

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)).

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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'.
0 Votes 0 ·
NaomiNNN avatar image NaomiNNN akhterhussain-3167 ·

Tibor meant CAST function but accidentally wrote CONVERT. Check https://docs.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.

0 Votes 0 ·
StacyClark-5656 avatar image
0 Votes"
StacyClark-5656 answered TiborKaraszi commented

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

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

"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

0 Votes 0 ·