What sort of date format needed here? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

What sort of date format needed here?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-05-30 : 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

354 Posts

Posted - 2012-05-30 : 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

15732 Posts

Posted - 2012-05-30 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-30 : 14:04:38

What do you mean by no data? Do you mean it returns a zero (0)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources