sql function for day of week
-
I am in great confusion.:confused:I wrote a sql function to find the day of week.My query is shown below: ALTER FUNCTION dbo.udf_DayOfWeek (@dtDate datetime) RETURNS VARCHAR(10) AS BEGIN DECLARE @rtDayofWeek VARCHAR(10) SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' END RETURN (@rtDayofWeek) END GO Call this function like this: SELECT dbo.udf_DayOfWeek('8/27/2010') AS DayOfWeek But I have to get output when fromdate and todate is given.ie:When fromdate='8/25/2010' and todate='8/27/2010',then the output is wednesday,Thursday,Friday..How to wrie sql function for this.Please modify the above code..Hope u will understand my problem Thanks in advance
-
I am in great confusion.:confused:I wrote a sql function to find the day of week.My query is shown below: ALTER FUNCTION dbo.udf_DayOfWeek (@dtDate datetime) RETURNS VARCHAR(10) AS BEGIN DECLARE @rtDayofWeek VARCHAR(10) SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' END RETURN (@rtDayofWeek) END GO Call this function like this: SELECT dbo.udf_DayOfWeek('8/27/2010') AS DayOfWeek But I have to get output when fromdate and todate is given.ie:When fromdate='8/25/2010' and todate='8/27/2010',then the output is wednesday,Thursday,Friday..How to wrie sql function for this.Please modify the above code..Hope u will understand my problem Thanks in advance
So change your function to take 2 dates and return a table In your function create a table variable with 1 or 2 fields (date and DoW) insert the dates and calc the DoW return the table to the procedure You can then do a join between the date passed in abd the date in the result set.
Never underestimate the power of human stupidity RAH
-
I am in great confusion.:confused:I wrote a sql function to find the day of week.My query is shown below: ALTER FUNCTION dbo.udf_DayOfWeek (@dtDate datetime) RETURNS VARCHAR(10) AS BEGIN DECLARE @rtDayofWeek VARCHAR(10) SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' END RETURN (@rtDayofWeek) END GO Call this function like this: SELECT dbo.udf_DayOfWeek('8/27/2010') AS DayOfWeek But I have to get output when fromdate and todate is given.ie:When fromdate='8/25/2010' and todate='8/27/2010',then the output is wednesday,Thursday,Friday..How to wrie sql function for this.Please modify the above code..Hope u will understand my problem Thanks in advance
If you use DATEPART(dw,'8/27/2010') that will give you a day of week. This may need adjusted for how the sys date is set. Also see @@datefirst
-
I am in great confusion.:confused:I wrote a sql function to find the day of week.My query is shown below: ALTER FUNCTION dbo.udf_DayOfWeek (@dtDate datetime) RETURNS VARCHAR(10) AS BEGIN DECLARE @rtDayofWeek VARCHAR(10) SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' END RETURN (@rtDayofWeek) END GO Call this function like this: SELECT dbo.udf_DayOfWeek('8/27/2010') AS DayOfWeek But I have to get output when fromdate and todate is given.ie:When fromdate='8/25/2010' and todate='8/27/2010',then the output is wednesday,Thursday,Friday..How to wrie sql function for this.Please modify the above code..Hope u will understand my problem Thanks in advance
DECLARE @rtDayofWeek VARCHAR(10)
DECLARE @dtDate datetime
DECLARE @endDate datetime
SET @dtDate = '8/25/2010'
SET @endDate = '8/27/2010'
WHILE @dtDate <= @endDate
BEGIN
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END
PRINT @rtDayofWeek
SET @dtDate = DATEADD(Day,1,@dtDate)
ENDHope this helps buddy :D
None :D