IF THEN Statement in SQL - 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

SQL Server 2008 Forums

Transact-SQL (2008)

IF THEN Statement in SQL

Author

Topic

MKz71
Starting Member

30 Posts

Posted - 2011-11-29 : 16:41:15

I Have the following SQL query that is blowing up on me. I am trying to SET a parameter to something within the query. Here is the code:

DECLARE 
	@TheMostRecentODInTheTable FLOAT,
	@TheMostRecentWallInTheTable FLOAT,
	@TotalWeight FLOAT,
	@CurrentShiftStartDateTime DATETIME

IF DATEPART(Hh,GETDATE()) BETWEEN 7 AND 18
	SET @CurrentShiftStartDateTime = DATEADD(Hh,DATEPART(Hh,7,DATEADD(dd,0, DATEDIFF(dd,0,GETDATE()))))
IF DATEPART(Hh,GETDATE()) BETWEEN 19 AND 23
	SET @HurrentShiftStartDateTime = DATEADD(hh,DATEPART(Hh,19,DATEADD(dd,0, DATEDIFF(dd,0,GETDATE()))))
ELSE
	SET @CurrentShiftStartDateTime = DATEADD(hh,DATEPART(hh,19,DATEADD(dd,-1, DATEDIFF(dd,0,GETDATE()))))

SELECT TOP(1) @TheMostRecentODInTheTable = OD, @TheMostRecentWallInTheTable = WALL 
FROM TRK.FCOProduction
ORDER BY ID DESC

SELECT SUM(CALC_PIPE_WT)/2000 AS TotalWeight
FROM TRK.FCOProduction 
WHERE 
    OD = @TheMostRecentODInTheTable AND 
    WALL = @TheMostRecentWallInTheTable AND
    dtTimeStamp >= @CurrentShiftStartDateTime 
GROUP BY OD, WALL

The IF Statements are where this is blowing up. Here are the errors:

Msg 174, Level 15, State 1, Line 8
The datepart function requires 2 argument(s).
Msg 174, Level 15, State 1, Line 10
The datepart function requires 2 argument(s).
Msg 174, Level 15, State 1, Line 12
The datepart function requires 2 argument(s).

Does anyone see what I am trying to do here? I am trying to look at the current hour and then set the @CurrentShiftStartDateTime according to the current hour when this is run.

If the current hour is between 7am and 6:59pm the shift should be the current date and 7:00AM (XXXX-XX-XX 07:00:00.000).

If the current hour is between 7pm and 11:59pm the shift should be the current date and 7:00PM (XXXX-XX-XX 19:00:00.000).

If the current hour is between 12am and 6:59am the shift should be the current date and 7:00PM on the day before (XXXX-XX-(-1) 19:00:00.000).

Anyone have any thoughts on how to get this to work correctly?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-29 : 18:49:56

The DATEPART has three aguments but takes only two.[CODE]SET @CurrentShiftStartDateTime = DATEADD(Hh,DATEPART(Hh,7,DATEADD(dd,0, DATEDIFF(dd,0,GETDATE()))))[/CODE]You have "Hh" as the 1st parameter, "7" as the 2nd and "DATEPART(etc.)" as the 3rd.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

MKz71
Starting Member

30 Posts

Posted - 2011-11-30 : 07:41:06

I found the problem... I put a parenthesis end bracket in the wrong location. It should be:

SET @CurrentShiftStartDateTime = DATEADD(hh,DATEPART(hh,7),DATEADD(dd,0, DATEDIFF(dd,0,GETDATE())))

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-30 : 11:06:59

Life is good, again...

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources