DatePart - SQL Server Forums

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

DatePart

Author

Topic

gualm
Starting Member

11 Posts

Posted - 2008-04-21 : 10:42:35

CREATE PROCEDURE [dbo].[sp_TRAK_PROG_TOTALS]
@Frequency varchar (1),
@Rpt_Yr smallint,
@Prog_Yr varchar (2)
AS

SET NOCOUNT ON
DECLARE
@PROG1 VARCHAR (20),
@PROG2 VARCHAR (20);
--@PROG_YR VARCHAR (2);

SET @PROG1 = 'TRAK08'
SET @PROG2 = 'TRAK208'
SET @PROG_YR = Select DatePart(YY, GetDate()) as Current_Year

SELECT @Rpt_Yr = case @Frequency
WHEN 'M' then (select dateadd(mm,datediff(mm,0,getdate())-1,0))
WHEN 'O' then convert(varchar,@Start_Date,101) + ' 00:00:00'
ELSE convert(varchar,getdate()-1,101)
end
--------------------------------------------------------------------------------------------------
I have done most ot the script for the rest of my pgoram but I am having problems with the date requirements.
I was told to parse the year so that the user can enter the 2 digit date.

When it is 'O' (other) was told build a string to parse the year using datepart so that the user can enter the program year (2 digit format). (@Rpt_Yr)
When it is 'M' (monthly) then I am to goto to table tk_prog get the active program
I have no clue how to correct the above.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 10:55:54

What is the correlation between user supplied parameters and algorithm to "SELECT @RPT_YR"?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 14:05:18

1) @Rpt_Yr is declared as smallint
When @Frequency equals "m" you will get strange value, if any, with that calculation.
When @Frequency equals "o" you will get strange value, if any, with that calculation.
When @Frequency equals anything else you will get strange value, if any, with that calculation.

2) @Prog_yr is declared as varchar(2)
When you use DATEPART, you will only get the first two characters "20" from the year.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources