So I need to select and average a whole bunch of data by week. Currently, I group by:
GROUP BY TO_CHAR(m.ENDTIME, 'IW')
and select using
SELECT ... TO_CHAR(m.ENDTIME, 'IW') Week
to summarize by week.
Since the first day of the year was a monday, it sets the weeks as monday-monday. I need to set the weeks to be Thursday 7pm to Thursday 7pm. How can this be achieved? Thanks.
So I need to select and average a whole bunch of data by week. Currently, I group by:GROUP BY TO_CHAR(m.ENDTIME, 'IW') and select using SELECT ... TO_CHAR(m.ENDTIME, 'IW') Week to summarize by week. Since the first day of the year was a monday, it sets the weeks as monday-monday. 2008 will start the week on Tuesday. I need to set the weeks to be Thursday 7pm to Thursday 7pm. How can this be achieved? I'm not sure if datefirst is the answer. Can it accept decimals? Is there another way to do this? I'm placing this query into a SqlDataSource selectcommand. Unfortunately I do not have the option of building a stored procedure. Thanks.
I need a Select sentence that return me the first week of the month for a given week.
For example If I have week number 12 (Begins 2015/03/16 and Ends 2015/03/22) I need that returns 9, I mean Week number 9 wich is the first week of march (having in mind @@DATEFIRST).
I only need give a week number of the year and then returns the week number of the first week of that month.
I wrote the following function to find the start of week date for a given date and a given start day of week.
For example: If the day passed is Saturday, 2005-03-19, and Sunday is the start of the week, it returns: 2005-03-13 00:00:00.000
If the day passed is Monday, 2005-03-14, and Sunday is the start of the week, it returns: 2005-03-13 00:00:00.000
If the day passed is Monday, 2005-03-14, and Monday is the start of the week, it returns: 2005-03-14 00:00:00.000
Does anyone have a simpler algorithim for start of week that they care to post?
Edit (2006/4/15): Posted a companion function F_END_OF_WEEK, on this topic: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760
There are other Start of Time Period Functions posted here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
There are other End Date of Time Period Functions here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
create function dbo.F_START_OF_WEEK ( @DATEdatetime, -- Sun = 1, Mon = 2, Tue = 3, Wed = 4 -- Thu = 5, Fri = 6, Sat = 7 -- Default to Sunday @WEEK_START_DAYint= 1 ) /* Find the fisrt date on or before @DATE that matches day of week of @WEEK_START_DAY. */ returnsdatetime as begin declare @START_OF_WEEK_DATEdatetime declare @FIRST_BOWdatetime
-- Check for valid day of week if @WEEK_START_DAY between 1 and 7 begin -- Find first day on or after 1753/1/1 (-53690) -- matching day of week of @WEEK_START_DAY -- 1753/1/1 is earliest possible SQL Server date. select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7)) -- Verify beginning of week not before 1753/1/1 if @DATE >= @FIRST_BOW begin select @START_OF_WEEK_DATE = dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW) end end
return @START_OF_WEEK_DATE
end go
-- Sample function calls
select dbo.F_START_OF_WEEK(getdate(),default) -- Returns Date for Sunday
select dbo.F_START_OF_WEEK(getdate(),1)-- Returns Date for Sunday select dbo.F_START_OF_WEEK(getdate(),2)-- Returns Date for Monday select dbo.F_START_OF_WEEK(getdate(),3)-- Returns Date for Tuesday select dbo.F_START_OF_WEEK(getdate(),4)-- Returns Date for Wednesday select dbo.F_START_OF_WEEK(getdate(),5)-- Returns Date for Thursday select dbo.F_START_OF_WEEK(getdate(),6)-- Returns Date for Friday select dbo.F_START_OF_WEEK(getdate(),7)-- Returns Date for Saturday
I thought I would also post an alternate way of doing the Start of Week instead of using the F_START_OF_WEEK function. These queries demo doing the Start of Week inline in a query, and use a similar algorithm to find the start of week, but the start day of week is hard coded.
I posted two versions. The first version is simpler, but it has a minor flaw that returns a false result if the start of week would be before 1753/1/1. For the vast majority of applications this would not be a problem. In the second, the algorithm is modified slightly to cause it to overflow if you pick a date that would result in a start of week before 1753/1/1. Note that the F_START_OF_WEEK function returns a NULL in this situation.
The demo queries use the F_TABLE_NUMBER_RANGE that is posted in another thread in order to generate dates to demonstrated the results: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
-- First demo query for Start of Week -- Returns bad result if the start of week would be before 1753/1/1 select DATE, Sun = dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684), Mon = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690), Tue = dateadd(dd,(datediff(dd,-53689,a.DATE)/7)*7,-53689), Wed = dateadd(dd,(datediff(dd,-53688,a.DATE)/7)*7,-53688), Thu = dateadd(dd,(datediff(dd,-53687,a.DATE)/7)*7,-53687), Fri = dateadd(dd,(datediff(dd,-53686,a.DATE)/7)*7,-53686), Sat = dateadd(dd,(datediff(dd,-53685,a.DATE)/7)*7,-53685) from ( select DATE = convert(datetime,number) from F_TABLE_NUMBER_RANGE(36524,40000) ) a
-- Second demo query for Start of Week -- Modified to cause an error instead of returning a bad date -- if the start of week would be before 1753/1/1 select DATE, Sun = dateadd(dd,((datediff(dd,-53684,a.DATE+7)/7)*7)-7,-53684), Mon = dateadd(dd,((datediff(dd,-53690,a.DATE+7)/7)*7)-7,-53690), Tue = dateadd(dd,((datediff(dd,-53689,a.DATE+7)/7)*7)-7,-53689), Wed = dateadd(dd,((datediff(dd,-53688,a.DATE+7)/7)*7)-7,-53688), Thu = dateadd(dd,((datediff(dd,-53687,a.DATE+7)/7)*7)-7,-53687), Fri = dateadd(dd,((datediff(dd,-53686,a.DATE+7)/7)*7)-7,-53686), Sat = dateadd(dd,((datediff(dd,-53685,a.DATE+7)/7)*7)-7,-53685) from ( select DATE = convert(datetime,number) from F_TABLE_NUMBER_RANGE(36524,40000) ) a
Our reporting group would like to generate reports based on day of the week. Is there a function or other means of determining the day based on data in a datetime field? Thanks for any assistance in advance.
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.
I have a requirement to show Day of week in parameter drop down list in different order, actual order is Monday to Sunday (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday) in DayOfWeek dimension in cube.
But my requirement is to show Friday to Thursday(Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday) in DayOf Week parameter drop down list and report table. How I can get this requirement done.
I have a table with structure below :Table_A{PGradeID int IDENTITY(1,1),PName varchar(20)}If I delete all records on Table_A, and then fill some records,PGradeID will no longer start from 1 anymore, everybody knows that. Howto change it so PGradeID will start from 1 after all records wasdeleted?Thanks b4Resant
For the past couple of days, I have been trying to get my SQL Server to work with Distributed Views. I am created linked servers, linked server logins, set XACT Abort ON.
I am successful in running a select against the distributed view, but was unable to run an "INSERT"
When I try a simple insert, the query took 3:14 minutes. Then I get an error message like:
Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have checked that MSDTC is running and configured under a domain account on both machines - running sQL 2000 and win2K
I have been unsuccessful still after tinkering for several days. I have checked my network configurations and noticed that when I try to ping the other machine by name, I don't get a response. I can only get a response to a ping when I enter the IP address directly.
Could this be a problem? Also, I noticed that for some strange reason, whenever I ping from either machine it is showing an external IP - always the same one no matter which computer name I try to ping. Something like 209.xxx.xxx.xxx instead of the 192.xxx.xxx.xxx that I expect.
Finally, I thought that problem was possibly due to incorrect Active Directory configuration. I tried to remove both machines from the domain by changing them to a workgroup "TEST" instead of the domain.
When I restarted the PC, I am unable to start SQL Server. It shows the Red Stop sign. When I try to start it, it gives an error like: Service could not start because one or more dependencies failed.
When I add the machine back to the domain, SQL server starts working on reboot.
I have a SQL 2k5 ent. 64 bit on Windows 2003 Ent x64 edition.
The sql server was running fine under localsystem account. Recently I changed the service account to a local user (part of users group in windows) and implemented permissions according to KB article 283811. (have imlpemented the same on many other sql servers without issue).
However, the SQL services are not starting up and I am seeing the following errors in the errorlog.
===================
2007-07-25 12:36:55.90 spid11s Server name is 'HBCARPROD'. This is an informational message only. No user action is required.
2007-07-25 12:36:55.90 spid13s Starting up database 'model'.
2007-07-25 12:36:55.90 Server Error: 17182, Severity: 16, State: 1.
2007-07-25 12:36:55.90 Server TDSSNIClient initialization failed with error 0x5, status code 0x51.
2007-07-25 12:36:55.90 Server Error: 17182, Severity: 16, State: 1.
2007-07-25 12:36:55.90 Server TDSSNIClient initialization failed with error 0x5, status code 0x1.
2007-07-25 12:36:55.90 Server Error: 17826, Severity: 18, State: 3.
2007-07-25 12:36:55.90 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-07-25 12:36:55.90 Server Error: 17120, Severity: 16, State: 1.
2007-07-25 12:36:55.90 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
====================================
I have gone through the http://blogs.msdn.com/sql_protocols but everything mentioned here seems to be there. I understand this is some permission issue. Just not able to find it out. Can anybody help?
I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like
01/01/2004 00:01:00
01/01/2004 00:02:00
01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00
I will use this time dimension in bussiness objects.
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 am trying to list all projects managed by technicians who joined in the year 2005 or later. the techno column is in both tables(test_technicians & test_projects) and the projno is only in the test_projects table. Whats wrong with my coding? I keep getting an error message that the FROM statement is in the wrong place
SELECT techno, projno Tstartdate TO_CHAR(Tstartdate,'YYYY') AS Tech Start Date FROM test_technicians, test_projects WHERE test_technicians.techno = test_projects.techno AND TO_CHAR(Tstartdate,'YYYY') >= '2005';
In oracle i use the following sentence TO_CHAR(Table1.DateIn, ,'mm/yyyy') within a query. I need how to replace this for using it in SQL Server. I tried to use the SQL Server CONVERT function but nothing work. Could you help me?
I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.
At the moment, I'm doing this:
Code Snippet
create member [MIA DW].[DATE TIME].[Date].[Yesterday] as [DATE TIME].[Date].&[2007-01-01T00:00:00]
select [Measures].members on 0, non empty [DATE TIME].[Date].members on 1 from [MIA DW] But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?
So I guess there's two questions:
1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)
2)How come the Yesterday member is not returned by the .members function?
I am struggling with SQL Server 2008 database in cyrillic language I try to Load data with ETL. But, both of the datetime columns' value are shown as 4015-01-01 as of 2015-01-01.How can i load data in Regular datetime format without parsing the column?
while(select MAX(wrh) from @tem1 where wrh = 0) < 1 begin update @tem1 set wrh = (select toaccount from @tem1 where reportdate = (select min(reportdate) from @tem1 where wrh = 0))+(select max(wrh) from @tem1) where wrh = (select max(wrh) from @tem1 where wrh = 0 ) and reportdate = (select min(reportdate) from @tem1 where wrh = 0) end
this is the result while executing loop statement .
employeeidreportdatereportatleftatdehdrhwehwrh 129029 Jan 201409:3019:15008:0009:20024:00065:54 129028 Jan 201409:0018:45008:0009:18016:00056:34 129027 Jan 201409:0018:45008:0009:18008:0009:18 129025 Jan 201408:0010:00005:0002:00045:00047:16 -- week end 129024 Jan 201409:1718:45008:0009:01040:00045:16 129023 Jan 201409:1918:46008:0009:06032:00036:15 129022 Jan 201409:1718:47008:0009:05024:00027:09 129021 Jan 201409:1618:35008:0008:46016:00018:04 129020 Jan 201409:1818:55008:0009:03008:0009:03
How to update only that week hrs , don't continue next week...
In my reports I am extracting the data of number of people joined in all the weeks of the year. And in one of reports I have to extract the data of the number of people joined until the last week from the first week. I am trying out all the logics but nothing is working for me as such. Can any one help me with this issue??????
Ilve install sql server 2012 in my pc and i want to change datetime default format. How can i do this and please i dont want to take the result from select convert() or select cast or something like this. I ve want to take the format i want writing query select datecolumn from table.
Now the format i have is: 2015-11-16 09:04:00.000
And i want this format: 16.11.2015 09:04:00
Is any way to convert automaticaly by select only column? or can i change at all once? or must write function to when i select the column can change automatic ? or another thing, i ve see in column properties something like formula. In computed column specification in formula i wrote this:
And I take the format i want automaticaly but i get the current date for all rows and i cant edit or insert that column anymore. So, how to change the format of date time but no from select query.
I am using Execute sql task in my SSIS package, and I am trying to make the following query:
<o:p></o:p> Select max(sqlid) from archive.dbo.Archivebbxfbhdr where timein <= ?<o:p></o:p> Where ? is my input parameter variable migration_start which is a datetime.<o:p></o:p>
My issue is that variable name migration_start which give me default format of 6/11/2015 1:26 AM
But I expecting to get in 2015-06-11 01:26:22.813 format.<o:p></o:p>
How I can I change the datetime format of my variable to be (yyyy/mm/dd)hh:mm:ss)?<o:p></o:p>
This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.
From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.
When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."
I have a query that run every day to update a summary table which has week number and day of week. what I currently do is delete all records from the summary table and then summarize all the data availabe from four tables adn then populate the table daily. I want to know if I can run the update query to run only for the week number and day of week depending on getdate. Can I do this?
Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. W01 represents the week of the year from W01 through W53, and D represents the day of the week with 1 = Monday through 7 = Sunday.
The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.
This code creates the function and demos it for the first day, first date+60, and first date+364 for each ISO week/year from 1990 to 2030.
drop function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK GO create function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK ( @Datedatetime ) returnsvarchar(10) as /* Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. */ begin
declare @YearWeekDayOfWeekvarchar(10)
Select --Format to form YYYY-W01-D @YearWeekDayOfWeek = convert(varchar(4),year(dateadd(dd,7,a.YearStart)))+'-W'+ right('00'+convert(varchar(2),(datediff(dd,a.YearStart,@Date)/7)+1),2) + '-'+convert(varchar(1),(datediff(dd,a.YearStart,@Date)%7)+1) from ( select YearStart = -- Case finds start of year case whenNextYrStart <= @date thenNextYrStart whenCurrYrStart <= @date thenCurrYrStart elsePriorYrStart end from ( select -- First day of first week of prior year PriorYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aaa.Jan4))/7)*7,-53690), -- First day of first week of current year CurrYrStart = dateadd(dd,(datediff(dd,-53690,aaa.Jan4)/7)*7,-53690), -- First day of first week of next year NextYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aaa.Jan4))/7)*7,-53690) from ( select --Find Jan 4 for the year of the input date Jan4= dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0)) ) aaa ) aa ) a
return @YearWeekDayOfWeek
end go
-- Execute function on first day, first day+60, -- and first day+364 for years from 1990 to 2030.
select DT= convert(varchar(10),DT,121), YR_START_DT = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT), YR_START_DT_60 = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+60), YR_START_DT_365 = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+364) from ( select DT = getdate()union all select DT = convert(datetime,'1990/01/01') union all select DT = convert(datetime,'1990/12/31') union all select DT = convert(datetime,'1991/12/30') union all select DT = convert(datetime,'1993/01/04') union all select DT = convert(datetime,'1994/01/03') union all select DT = convert(datetime,'1995/01/02') union all select DT = convert(datetime,'1996/01/01') union all select DT = convert(datetime,'1996/12/30') union all select DT = convert(datetime,'1997/12/29') union all select DT = convert(datetime,'1999/01/04') union all select DT = convert(datetime,'2000/01/03') union all select DT = convert(datetime,'2001/01/01') union all select DT = convert(datetime,'2001/12/31') union all select DT = convert(datetime,'2002/12/30') union all select DT = convert(datetime,'2003/12/29') union all select DT = convert(datetime,'2005/01/03') union all select DT = convert(datetime,'2006/01/02') union all select DT = convert(datetime,'2007/01/01') union all select DT = convert(datetime,'2007/12/31') union all select DT = convert(datetime,'2008/12/29') union all select DT = convert(datetime,'2010/01/04') union all select DT = convert(datetime,'2011/01/03') union all select DT = convert(datetime,'2012/01/02') union all select DT = convert(datetime,'2012/12/31') union all select DT = convert(datetime,'2013/12/30') union all select DT = convert(datetime,'2014/12/29') union all select DT = convert(datetime,'2016/01/04') union all select DT = convert(datetime,'2017/01/02') union all select DT = convert(datetime,'2018/01/01') union all select DT = convert(datetime,'2018/12/31') union all select DT = convert(datetime,'2019/12/30') union all select DT = convert(datetime,'2021/01/04') union all select DT = convert(datetime,'2022/01/03') union all select DT = convert(datetime,'2023/01/02') union all select DT = convert(datetime,'2024/01/01') union all select DT = convert(datetime,'2024/12/30') union all select DT = convert(datetime,'2025/12/29') union all select DT = convert(datetime,'2027/01/04') union all select DT = convert(datetime,'2028/01/03') union all select DT = convert(datetime,'2029/01/01') union all select DT = convert(datetime,'2029/12/31') union all select DT = convert(datetime,'2030/12/30') ) a