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
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
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
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
hi guys,
How could I convert 'week 9 2008' back into the first day of that week ?
is that even possible ?
Hi,
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.
seriously now
View 1 Replies View RelatedHi all!
I'm working on a report that will look like this.
WeekNb - Item - QtySold
1 - Item1 - 25
1 - Item2 - 6
2 - Item1 - 10
2 - Item3 - 73
etc...
I use this function to get the week number DATEPART(wk, transactionDate)
But now I have a problem,
Lets says I want the sells for 2008.
The 1st of January is a Tuesday, but I also want the sells that I made on the 31st of December 2007 as it's on the 2008 1st week (monday).
How can I get this to work ?
Help please
Regards,
Or Tho
I am working with Week Numbers and was wondering if there is a way to display Week 1 as 01.
The problem that I am having is that when I list the weeks from Week 1 to Week 12 is that the sorting is 1, 10, 11, 12, 2, 3 and I would like to get it to look like 01, 02, 03....09, 10.
Thanks......
Hi Chaps,
I want to calculate the week number by the following rule
"For the week that has the 1st January included if any days are not in January then set the next week as week number 1"
Can any tell me the piece of code
thanx
Anas
Any easier way to do what I am trying, without having a table with all the dates and week numbers.
Scenario: Week number of a DateTime Field where the year does not start on January 01<sup>st</sup> but April 1<sup>st</sup> to 31 Mach.
Issue: A week always starts on a Monday so if the 1<sup>st</sup> is on Tuesday, the first week is Tuesday – Sunday, if the 1<sup>st</sup> April is on Friday, the 1<sup>st</sup> week is Friday – Sunday and 7 day periods from there.
CREATE
TABLE [dbo].[DailyCanx](
[ID] [int] IDENTITY(1,1)
NOT NULL,
[DateCancelled] [datetime] NULL
)
ON [PRIMARY]
[code]....
I could create a table with all the start date and end dates of all the week numbers but I think there must be a better way. using SQL Server 2008 R2.
SQL express 2012. I am trying to case in the where part and having a syntax errors - This is what i am trying to do:
select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime
[Code] ....
SQL express 2012
I am trying to case in the where part and having a syntax errors - this is what i am trying to do:
Select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime
[Code] ...
I 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?
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....
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 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
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!