dears, it so weird but the issue has been fixed suddenly!!!

I've deleted the table and recreate it again by making all columns defeminations as "nvarchar" then run the query and...

yes, it was by mistake and have been fixed, however the problem still exist is the both mentioned columns.

I also tried to change the columns definitions to "nvarchar" instead of...

attached the full table definitions.

thanks for your reply, actually in my case the ID field is already nvarchar as attached figure, however I've test your proposal but still getting the same error.

yes, it works.

appreciate your reply.

the main problem that I face now is the employees could be entitle for 21 days each 365 for a certain time then he will be treated by 30 days...

Since it's a calculation based on the current state of the data couldn't you just use a query?  Why does the balance have to be stored?  Perhaps...

Since it's a calculation based on the current state of the data couldn't you just use a query?  Why does the balance have to be stored?  Perhaps it could...

Hi Scott,

I've added the table structure and sample...

sorry, I forgot to put sample data for holiday table, here it's

CREATE TABLE [dbo].[HOLIDAYS_LR](
[nHoliday] [varchar](1) NULL,
[hDate] [date] NULL,
[hEnddate] [datetime] NULL
) ON [PRIMARY]

INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate)...

• This reply was modified 4 years, 1 month ago by  tanehome.
• This reply was modified 4 years, 1 month ago by  tanehome.
• This reply was modified 4 years, 1 month ago by  tanehome.
• This reply was modified 4 years, 1 month ago by  tanehome.

Hi all again

the function works fine for two weeks as long as the entered DateFrom is not a Holiday (it counts it as a working day) but I think it's...

• This reply was modified 4 years, 1 month ago by  tanehome.
• This reply was modified 4 years, 1 month ago by  tanehome.
• This reply was modified 4 years, 1 month ago by  tanehome.
• This reply was modified 4 years, 1 month ago by  tanehome.

sorry, it seems that i need to sleep 🙂

i forget to divide by 60, it should be as below

SELECT  CONVERT(CHAR(5), DATEADD(MINUTE, 60*(DATEDIFF(MINUTE,CAST(@hFrom +':'+@mFrom AS TIME), CAST(@hTo...

if i need it to does not include date swap, means...

>> failed to convert the below scalar function to table valued function, i tried many times and always got error <<

Actually, your problem is that you're not writing SQL...

Thank you ScottPletcher so much, appreciate your effort and assist.

that's exactly what i need, but i forced to remove "WITH SCHEMABINDING" because it gives me an error as i mentioned...

• This reply was modified 4 years, 1 month ago by  tanehome.