If I use Datepart(wk, getdate()) to get the current week, is there a way to get the date for the first day of the week that is returned?
I have a query that I group everything by week number, but would like to show the actual date for the beginning of the week.
Any help or guidance would be appreciated....
How can I get Saturday's date given the week number?
This is my week number, SELECT DATEPART(WEEK, DATEADD(MONTH, +3, ApptDt2)). I need to get Saturday's date from this week.
I'm looking for a way to convert the [week number, year] back to the date for the 1st day of that week.
Example (assuming DATEFIRST has been set to 1) : Week 3, 2008 = January 14, 2008
Is there a SQL function that will do this?
Thanks,
Mike
seriously now
View 1 Replies View RelatedI need to write a user defined function that will return the date of the first day of a week when provided with the week number. I had an idea of using a while loop with that will keep adding 1 day from January 1st until the week number (found via the DATEPART function) is equal to the supplied week number. Not sure if this is the best way though - any ideas?
Donald.
I'm wanting to see if there is a simpler way to accomplish the retrival of the first day of the week Date. What I currently have is:
CASE
WHEN DATEPART(DW, GETDATE()) = 1 THEN DATEADD([D], - 7, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 2 THEN DATEADD([D], - 8, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 3 THEN DATEADD([D], - 9, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 4 THEN DATEADD([D], - 10, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 5 THEN DATEADD([D], - 11, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 6 THEN DATEADD([D], - 12, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 7 THEN DATEADD([D], - 13, GETDATE()) END AS StartWeek1
I'm trying to figure if there might be an easier way to code this. Thanks.
Hi,
How would it be possible to calculate the next date of a given week day.
So if today is 6th April 2008 find the date of the next wednesday (i.e. the up and coming wednesday) of the next saturday etc.
You help is appreciated.
Cheers
Hey, Im pretty sure this is possible, but let me know if it isnt.I have a table of dates in the format "DD/MM/YYYY", is there a way of converting it to output the day of the week? for example 16/11/2007 would display Friday insted.Thanks in advance John
View 3 Replies View RelatedHi,
I want to convert a date to a weeknumber in my view.
How is this possible with SQL?
Greetings,
Any tips on using datediff to get last dat of week( in this case saturday) from a date input in SQL?
Thanks for any help!
How to select a date a week ago?
Below is something i want
10/04/2006 ->09/27/2006
my code is like below:
SELECT CONVERT(CHAR(10),GETDATE(),101) - day(7)
but it cant work.
Hi
I need help with a query that returns todays date -one week
Ex: the 1 december it should return "24" for 24 november.
this one below works only from the 7th every month else it give me a negative number :)
SELECT RIGHT ('0' + CAST(DAY(GETDATE()) - 7 AS varchar); 2)
Thx in advance
//Mr
I have week number and i want to get the date from that
for example Week=52 will be the first day of that day which is monday 12/24/2007
Thanks
I have a column in my dataset that contains the week number.
I would like to use 'Expression' Or code function to do that
how can i get the date from week number? for example the week number is 38
so i wil get this output: 09/17/07 - 09/23/07 (the week starts on Monday and ends on Sunday)
Thanks
I need SQL to determine what the date range for the previous week was, ie
Start date 11/11/01 for Last Sunday through 11/17/01 the following Saturday.
The results will populate a drop down box for user queries.
Thanx.
Hi Guys,
Can anyone please let me know How to find the first day of the week, if I know the weekno and the year.
For ex: If I know the week no is 22 and Year is 2003 then I should find a way to output the date. which is the first day (monday) of that week
for the above query the date will be eg:26/06/2003
I really appreciate your Help
Thanks:confused: Find the Date if I know the week and year
i have this SQL table with a year, week and day-of-week field, but no field containing the actual date
is there a way (e.g. in a view of the SQL table) to transform the year/week/day combination into a date string (e.g. format dd/mm/yyyy) ?
How do i automate, by stored procedure, generating the "week ending" date using a field data.
------------------------------------------------------------
CREATE TABLE [dbo].[t_Work_Hours] (
[WorkHoursID_PK] [int] IDENTITY (1, 1) NOT NULL ,
[PeopleID_FK] [int] NOT NULL ,
[JobID_FK] [int] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[Title] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeekEnding] [datetime] NULL ,
CONSTRAINT [PK_t_Work_Hours] PRIMARY KEY CLUSTERED
(
[WorkHoursID_PK]
) ON [PRIMARY] ,
CONSTRAINT [FK_t_Work_Hours_t_Work_People] FOREIGN KEY
(
[PeopleID_FK]
) REFERENCES [dbo].[t_Work_People] (
[PeopleID_PK]
)
) ON [PRIMARY]
GO
-------------------------------------------------------
What i have tried has'nt even been close to a solution.
UPDATE dbo.t_Work_Hours
SET WeekEnding = DATETIME ( ??? , StartTime) . I have no idea where to go!
----------------------------------------------------------------
WorkHoursID_PK, PeopleID_FK, JobID_FK, StartTime, EndTime, Title, WeekEnding
7, 40, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, J, (NULL)
8, 43, 3, 10/31/2005 6:00:00 AM, 10/31/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
9, 43, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
-----------------------------------------------------------
Can anyone explain the result of the sql statement
Select DATEADD(wk, DATEDIFF(wk, 6,getdate()), 6)
Can we use this to get the start date of the current week always???
I want to extract day of the week from given date.
If the given date is 07-03-2014(dd-mm-yyyy).IF i run the query the output should be =Friday
Can I get a date from a week number in a certain year, like I know it's week 7 in 2014 (I may know the month.)
View 7 Replies View RelatedI want to convert date to week consider saturday is first day of week and friday is end of week. for input is orderdate and output should be week. example orderdate 11.04.15 to 17.04.15 week should be 16. orderdate 18.04.15 week should change to 17.
Order DateWeek
11-Apr-201516
12-Apr-201516
13-Apr-201516
14-Apr-201516
15-Apr-201516
16-Apr-201516
17-Apr-201516
Order DateWeek
18-Apr-201517
There should be only 52 weeks in a year unless there is a leep week(these should not happen on back to back years).
What is going wrong?
SET DATEFIRST 7
select DatePart(Week, 'Dec 29, 1998'), 'Dec 29, 1998'
union
select DatePart(Week, 'Dec 27, 1999'), 'Dec 27, 1999'
union
select DatePart(Week, 'Dec 24, 2000'), 'Dec 24, 2000'
week of year
date
53
24-Dec-00
53
27-Dec-99
53
29-Dec-98
In my database the following dates have all been assigned to week 53
Year
Month
Day
1985
12
30
1985
12
31
1986
12
30
1986
12
31
1987
12
28
1987
12
29
1987
12
30
1987
12
31
1988
12
27
1988
12
28
1988
12
29
1988
12
30
1989
12
31
1990
12
31
1991
12
30
1991
12
31
1992
12
28
1992
12
29
1992
12
30
1993
12
27
1993
12
28
1993
12
29
1993
12
30
1994
12
27
1994
12
28
How do i get only the records for everything with only the dates from last week (monday-friday)? I want to have this run every week for the previous week. Here's my stored procudure. Right now i have this to run for the day before, but now i need it for the week before. So its a range. Please Help. Thanks!
Code Snippet
CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]
(@Classification varchar(50))
AS
BEGIN
SELECT RC_STAT.dbo.Brand_Dimension.Report_Level,
RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],
RC_STAT.dbo.Brand_Dimension.Item_Merged,
Qry_Sales_Group.Region_Key,
Qry_Sales_Group.Region,
Qry_Out_Of_Stock.product_structure_level,
Qry_Out_Of_Stock.product_entity_code,
Qry_Out_Of_Stock.cycle_day,
Qry_Out_Of_Stock.customer_code,
Qry_Out_Of_Stock.description,
Qry_Sales_Group.Code,
Qry_Sales_Group.SR_Name,
Qry_Sales_Group.Name AS Territory_Name,
Qry_Out_Of_Stock.Store_Name,
Qry_Out_Of_Stock.time_log,
Qry_Out_Of_Stock.out_of_stock,
Period_Code
FROM RC_STAT.dbo.Brand_Dimension INNER JOIN
dbo.Qry_Out_Of_Stock ON
RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN
dbo.Qry_Sales_Group ON
dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
Where Classification=@Classification DateDiff(dd,0,dbo.Qry_Out_Of_Stock.time_log) =
case
when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3
else datediff(dd,0,GetDate()) - 1
end
END
SET NOCOUNT OFF
Hi,
I don't know if this can be done, but hopefully one of the experts in this forum cna help me out.
I have a TimeOff table which contains 5 fields: timeoffID, employeename, startdate, enddate, and timofftype. I need to create a report which shows how many people take sick day (one of the timeofftypes) on each week day (i.e. Monday, Tuesday, Wednesday, and etc.) The problem is that I don't know how to convert the date range (from startdate to enddate) into indvidual week day for each timeoff record.
For example, on the table,
TimeoffID employeename startdate enddate timeofftype
1 Andy 11/02/2006 11/03/2006 Sick Day
2 Bill 11/03/2006 11/03/2006 Sick Day
The report needs to show:
Monday Tuesday Wednesday Thursday Friday Saturday Sunday
0 0 0 1 2 0 0
Thank you in advance!
hi guys,
How could I convert 'week 9 2008' back into the first day of that week ?
is that even possible ?
Hello all,
I have the following SQL statement that works great for my purpose:
SELECT DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0)) AS TimePeriod, AVG(ZNT) AS Expr1, AVG(SAT) AS Expr2, AVG(RAT) AS Expr3,
AVG(RH) AS Expr4
FROM HVACValues
GROUP BY DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0))
ORDER BY TimePeriod
I'd like to do everything above but add to display only those records from 1/1/07 to 1/7/07...so a week's worth.
I'm no SQL expert but I assume that I'd add 'WHERE TimePeriod = ....' Any help is much appreciated!
Thank you very much.
I require some scripting to return data which sits in a rolling weeks date range of the current week.
For example, on a Monday I want to return data from the previous day (Sunday to the previous Monday).
On a Tuesday I want to return data from the previous day (Monday to the up coming Sunday).
On a Wednesday I want to return data from the Monday (previous 2 days ago to the upcoming Sunday)
On a Thursday I want to return data from the Monday (previous 3 days ago to the upcoming Sunday)
And so forth.
So I have my SQL script which works with static, fixed date ranges. But I need this additional dynamic date range logic built in.
I need to run a select on Mondays to pull data for 7 days prior to the Thursday of last week; i.e. Friday - Thursday inclusive. I'm sure this is simple, but I work with dates so infrequently that I need a refressher.Â
View 7 Replies View RelatedHi,
Can anybody tell me to How to get the week number of a specific Date in SQL Server 2005.
Like : say Date "03/01/2008" belongs to the 1st week of the year 2008, same way "21/02/2008" is 8th week of the year 2008.
So, the same way I need to know the week number of a given (variable) date.
I need to write / use a Function in SQL server which will return the Number (INT) (i.e. 1 or 8 or 10 etc...) against a specific (Parameter) date.
regards,
Saibal
Note : Please feel free to write me if my question is not clear.
We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week.Â
I'm trying to produce a line chart with 2 axis:
- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc)
- y axis; the cumulative number of ordersÂ
The chart needs two series:
Previous Week. The running count of orders placed that week.Â
Current Week. The running count of orders placed this week.Â
Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day.Â
I have two tables:
Orders TableCalendar Table
The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc.Â
My measure for counting orders is simply; # Orders: = countrows[orders].
How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:
-Â [URL] ...
-Â [URL] ...
But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.
I can't seem to upload images so here's the link:Â [URL] ....
I need to get the last value of # Orders week avg in the current context (the highlighted value). The calendar dimension here is a week level one so I don't think I can use LASTNONBLANK. The measure is from the facts_sales table which is linked to a calendar dimension via a date key.Â
Hi All,
The current/ Base table would be like below,
Products
level
Date
N1
b
11/5/2007
N2
p
11/6/2007
N3
p
11/7/2007
N4
p
11/14/2007
N5
b
11/15/2007
N6
p
11/23/2007
Expected Result.
<=11/7/2007
<= 11/14/2007
<=11/21/2007
b
1
1
2
p
2
3
4
Total
3
4
6
As you can see, the above table has cumulative data.
1. It calculates the number of Products submitted till a particular date- weekly
2. The date columns should increase dynamically(if the dates in base table increases) each time the query is executed
For ex: the next date would be 11/28/2007
I tried something like, it gives me count of €˜b€™ level and €˜p€™ level products by week
declare @date1 as datetime
select @date1 = '6/30/2007'
while (@date1 != (select max(SDate) from dbo.TrendTable))
begin
set @date1 = @date1 + 7
select Level, count(Products)
from
dbo.TrendTable
where SDate < @date1
group by Level
end
what I think is required is a pivot that dynamically adds the columns for increase in date range.
/Pls suggest if any other way of achieving it.
Pls help!!!
Thanks & Regards