May 10, 2010 at 8:29 am
Hi all,
Could some one help me code this query which i got from access and want to have it as a stored procedure on SQL Server.
Here is the code from the query:
ActDate: Switch([timestart]<=#1:00:00 AM#,[Date]-1,True,[Date])
What I did understand from this is >> if TimeStart is less than or equal to 1:00:00 AM the subtract 1 from Date, else Date, and return it AS ActDate.
as a new SQL Server user, i found this really challenging for me, so that why i need your help !!
I can't create this in views because it actually take parameters.
May 10, 2010 at 9:51 am
You'll prbably need the CASE expression.
http://msdn.microsoft.com/en-us/library/ms181765.aspx
SELECT
'ActDate' =
May 10, 2010 at 10:01 am
Sorry sent the last reply off too soon.
You'll probably need the CASE expression.
http://msdn.microsoft.com/en-us/library/ms181765.aspx
CREATE TABLE #TEST
(TimeStart datetime)
INSERT INTO #TEST VALUES ('2010-05-10 00:30:00')
INSERT INTO #TEST VALUES ('2010-05-10 14:30:00')
SELECT
'ActDate' =
CASE
WHEN DATEPART(hour, TimeStart) <= 1 THEN getdate()-1
ELSE getdate()
END
FROM #TEST
DROP TABLE #TEST
May 10, 2010 at 10:04 am
thanks emily for the reply,,
but i wanted to know how would i write the code for the if statement itself,, i mean ,, TimeStart is a datetime field, how would i tell SQL to look into the time and if it is less than or equil to one 1 AM ,,,, i want to know how to code this part
"if TimeStart is less than or equal to 1:00:00 AM"
Thanks
May 10, 2010 at 10:18 am
Thanks emily,, it did work 🙂
May 10, 2010 at 10:24 am
In SQL Server 2005 a datetime field contains both date and time.
So this will extract the hour out of a datetime field-DATEPART(hour,Timestart)
Are you trying to control the flow of code execution in your stored procedure?
DECLARE @TimeStart datetime
SET @TimeStart = '2010-05-10 00:30:00'
PRINT @TimeStart
IF DATEPART(hour, @TimeStart) < = 1
BEGIN
PRINT 'its before 1AM'
END
ELSE
BEGIN
PRINT 'its after 1AM'
END
Or are you including this logic in a WHERE statement?
When you say less than 1 AM are you looking at both date and time or are you just concerned with the time.
Maybe you need the following to always returm 1 Am of the current date.
SELECT dateadd(hour,1, datediff(dd,0,getdate()))
You need to better describe what you are tyring to accomplish.
Read this also
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply