| Author |
 Topic  |
|
|
AdamWest
Constraint Violating Yak Guru
USA
302 Posts |
 Posted - 05/30/2012 : 12:50:59
|
this function is being used in an sp and i can see that there is no data being returned. i am using dates as 05-01-2012
also generally what is the purpose of this function would you say?
USE [PRO2]
GO
/****** Object: UserDefinedFunction [OPTC].[DBD_GET_ARCH] Script Date: 05/30/2012 12:46:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------
ALTER FUNCTION [OPTC].[DBD_GET_ARCH]
(@FACILITYKEY CHAR(4), @STARTDATE DATETIME, @ENDDATE DATETIME)
RETURNS NUMERIC
AS
BEGIN
DECLARE
@ARCH NUMERIC(1),
@SPLITDATE DATETIME,
@I NUMERIC(9, 0);
SELECT @SPLITDATE = NULLIF(SPLIT_DATE, CONVERT(DATETIME, '20000101', 112)) + 1
FROM OGEN.GEN_P_ARCHIVE
WHERE FACILITY_KEY = @FACILITYKEY;
IF @@ROWCOUNT = 0
SET @SPLITDATE = CONVERT(DATETIME, '20000102', 112);
SET @ARCH = 0;
IF @SPLITDATE <= @ENDDATE
SET @ARCH = @ARCH + 1;
IF @SPLITDATE >= @STARTDATE
SET @ARCH = @ARCH - 1;
RETURN @ARCH;
END |
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
Posted - 05/30/2012 : 13:37:53
|
Can you let us know what value will be retrieved for column SPLIT_DATE
from OGEN.GEN_P_ARCHIVE table
can you please provide the sample data of table OGEN.GEN_P_ARCHIVE
and it create table structure and let us know what value are being passed to this function .
Vijay is here to learn something from you guys. |
 |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 05/30/2012 : 13:55:05
|
The recommended format is ISO format, which is yyyymmdd. It doesn't technically matter since you're passing to a function that accepts datetime types. ISO format is unambiguous, whereas 05-01-2012 could be interpreted as either January 5 or May 1 depending on your regional or SQL settings.
The function itself returns a 0 if the SPLIT_DATE value for the facility is outside the date range passed to the function, and -1 or 1 if it's inside. As to why you're not getting data returned, the function supplies default dates if there's no data for that facility, so you should always get a value. |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 05/30/2012 : 14:04:38
|
| What do you mean by no data? Do you mean it returns a zero (0)? |
|
|
| |
Topic |
|
What sort of date format needed here?
