Formatted date information returned by a table function

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

SQL Server 2000 Forums

SQL Server Development (2000)

Formatted date information returned by a table function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-14 : 08:58:10

Ben writes "I would like to use a table function to display a set of records in a specific format. If is pass a single date ('01/20/2006') the function returns the values. When I try to replace this date with a field from a joined table, I get syntax errors. I would appreciate any help or improvement ideas that you have. Thank You.
OS = WINDOWS 2003 Server
SQL Server = SQL Server 2000
The function is:
/******
Object: User Defined Function dbo.DATEINFO
******/
CREATE FUNCTION DATEINFO(@MFG_DATE AS DATETIME)
RETURNS @DATEINFO TABLE
(
REPORT_DATE VARCHAR(10),
REPORT_YEAR VARCHAR(4),
REPORT_MONTH VARCHAR(2),
REPORT_WEEK VARCHAR(2),
REPORT_DAY_OF_YEAR VARCHAR(3),
REPORT_INPUT_DATE SMALLDATETIME
)
AS
BEGIN

DECLARE @DATE AS DATETIME
SET @DATE = @MFG_DATE

DECLARE @CurrentYear AS VARCHAR(4)
DECLARE @CheckYear AS VARCHAR(8)
DECLARE @MonthCount as int
DECLARE @rDATE AS VARCHAR(10)
DECLARE @rYEAR AS VARCHAR(4)
DECLARE @rMONTH AS VARCHAR(2)
DECLARE @rWEEK AS VARCHAR(2)
DECLARE @rDAY AS VARCHAR(3)

SET @CurrentYear = YEAR(@DATE)
SET @CheckYear = @CurrentYear+'0101'
SET @rDATE = CONVERT(VARCHAR(10),@DATE,101)
SET @rYEAR = DATEPART(yyyy,@DATE)
SET @rMONTH = DATEPART(mm,@DATE)
WHILE LEN(@rMONTH) < 2
BEGIN
SET @rMONTH = '0'+ @rMONTH
END
SET @rWEEK = DATEPART(ww,@DATE)
WHILE LEN(@rWEEK) < 2
BEGIN
SET @rWEEK = '0'+ @rWEEK
END
SET @rDAY = DATEDIFF(dd,@CheckYear,@DATE)+1

INSERT INTO @DATEINFO
(REPORT_DATE,
REPORT_YEAR,
REPORT_MONTH,
REPORT_WEEK,
REPORT_DAY_OF_YEAR,
REPORT_INPUT_DATE)
SELECT @rDATE,@rYEAR,@rMONTH,@rWEEK,@rDAY,@MFG_DATE
RETURN
END"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-14 : 09:46:02

Post the query you used that joins the function with your other tables

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-14 : 10:02:42

You are not going to be able to pass a value from a column in a query to a table function.

You should create a table with dates in it, and join to that table in your query.

You can use this table function, F_TABLE_DATE, to generate a table with the dates you need. You could also use this function in your query and join on column DATE to your other table.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

This output of function F_TABLE_DATE appears to have all the date fromats you require, plus many more. I am not sure about the numbering system that you are using for week, but many manufacturing companies follow the ISO week number system, with weeks starting on the Mondays. If that is what you company does, you can use one of the ISO week columns supplied for that, for example, ISO_YEAR_WEEK_NAME.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources