I have a UDF that returns the number of hours worked in a day from table.
@Consultant as nvarchar(20),
@DateofWork as Date
DECLARE @TotalTime decimal(18,2)
SELECT @TotalTime = SUM(Hours_Actual)
Member_ID = @Consultant
AND Date_start = @DateofWork
If I execute the following query
SELECT dbo.usp_GetDailyTime('bsmith', '07/05/2013') as Total
Returned value is 0
(SELECT SUM(Hours_Actual) as time
Member_ID = 'bsmith'
AND DateDiff(d, Date_start , '07/05/2013') = 0)
Returned value is 0.30 (This value is the total of time entries for the day. so this is correct)
The Field int he table is defined as a decimal(18.2)
If I modify the UDF to count the records it is seeing the correct number of records.
SQL versionMicrosoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Why did you put this in a UDF? It will not optimize and cannot port. Why do you avoid ISO-8601 date formats? Why did you confuse rows and records? Fields and columns? Your mindset is locked in procedural coding and you want to make your SQL look like COBOL, BASIC or whatever language you know.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
- Proposed as answer by Naomi NModerator Sunday, July 07, 2013 9:44 PM