I'm using VB.Net and SQL Server. Here's the scenario I have that I can't figure out how to accomplish:
(btw, this is all manual - I'm NOT using an ASP.Net Calendar control)
I want to provide a week's worth of calendar data automatically. Let's say I have all the calendar dates and times in a table. But - I only want to show the data from the current week.
So, no matter which day the end user loads the page (Monday, Tuesday, Saturday, etc), I want to be able to find the previous Sunday (or Monday?), and then provide data from the table for events that are happening from that Monday, through the next Sunday.
Finding the last Monday (or Sunday?) is the first problem, and then getting only data between that date and the following Sunday is the second problem - how to create the code and hw to create the sql statement - -
I have written a piece of SQL which I thought was wokring fine. I wanted to show all data for the current week from Monday - Sunday
I wanted to pick up all data from Monday 00:00:01 to Sunday 23:59:59 and then when the data runs again on the next Monday, a completley new set of data starts to be loaded in
On the off chance I have come into work today and realise that no data is being diaplyed, and today is a Sunday
I know for a fact that my SQL will pick up data as soon as it hits 00:00:01 tonight
I really want my SQL to pick up data for a Sunday
BETWEEN DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) AND DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 6)
I would like to show employee work hours daily from Monday to Sunday. I have managed to write a query to get the total hours.
This is the query giving total hours from and to date.
(select sum(t.timespent)/60 from timeitems t where t.employee = e.code and t.project = p.code and t.ndate >= '2014-07-15' AND t.ndate <= '2014-07-15') as Hours from projemplink pl
I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hard coded it but this is the real date which is falling inside the table.Â
Note : Week should be generated from Monday to Sunday within desired date range
I have time in the Integer Field ( The Field Represents milliseconds Past Midnight w.r.t GMT ) I want to have a function so that I can pass this integer field to the function and get the DATETIME Value.
In that function I want to code it for Day light Saving time also
How to get First Sunday of APRIL and Last Sunday of OCTOBER
Eg
ms past midnight w.r.t 47602656 SELECT 47602656 , DATEADD(hh , -5 , DATEADD(MS, 47602656 , CONVERT(VARCHAR(10), GETDATE(), 101)) )
i have this FUNCTION and the FUNCTION work OK now how to add to this FUNCTION another mondy date ? "last month last monday"
Code Snippet CREATE FUNCTION [dbo].[Monday_List]( @arg_date datetime ) returns table as return ( select convert(varchar(10), (dateadd(mm, datediff (mm, 0, @arg_date), 0) + (8 - datepart(dw, dateadd(mm, datediff (mm, 0, @arg_date), 0))) % 7) + offset, 103) as Date, [Index], Number from ( select 1 as Number, 'First_Monday' as [Index], 0 as offset union all select 2, 'Second_Monday', 7 union all select 3, 'Third_Monday', 14 union all select 4, 'Fourth_Monday', 21 union all select 5, 'Fifth_Monday', 28 ) x where month(dateadd(mm, datediff (mm, 0, @arg_date), 0) + (8 - datepart(dw, dateadd(mm, datediff (mm, 0, @arg_date), 0))) % 7) = month((dateadd(mm, datediff (mm, 0, @arg_date), 0) + (8 - datepart(dw, dateadd(mm, datediff (mm, 0, @arg_date), 0))) % 7) + offset) )
this is the Output i get
select * from Monday_List('1/3/8')
Date Index Number ---------- ------------- ----------- 03/03/2008 First_Monday 1 10/03/2008 Second_Monday 2 17/03/2008 Third_Monday 3 24/03/2008 Fourth_Monday 4 31/03/2008 Fifth_Monday 5 */
now i need to add to the FUNCTION "last month last monday" like this
select * from Monday_List('1/3/8')
Date Index Number ---------- ------------- ----------- 04/02/2008 last monday 0 ---------------------- i need to add 03/03/2008 First_Monday 1 10/03/2008 Second_Monday 2 17/03/2008 Third_Monday 3 24/03/2008 Fourth_Monday 4 31/03/2008 Fifth_Monday 5 */
I want to find the date of second sunday of March 2007 to March 2015 (For daylight saving rule).Is there any function in SQL Server which will retrun that? Thanks. I want following..
declare @year int select @year = 2007 select fn_findSecondMonday(@year) _________________________________ 03/11/2007 -- second sunday of March
I have a string which contains the year and month, now i wanted to know what is the starting day of that month in that particular year.
For Ex: string STR = "Jan2006" then by any chance i can know the starting day of this month as whether it is Sunday etc. I need the string "sunday" as output. Also, I need to know how many number of days that particular month contains. Any function or stored procedure which will give out these two outputs will be much appreciated.
I am writing an sql query as follow:SELECT DATEPART(dw, CALLSTARTTIME) AS dayFROM TABLEGROUP BY DATEPART(dw, CALLSTARTTIME)ORDER BY DATEPART(dw, CALLSTARTTIME)it returnsday====1234567my question is, how can I return monday, tuesday, wednesday, etc... insteand of 1-7?
I am using SQL Server 2000. I need to query my database for all thecontracts that came in during a certain time frame (user is promptedfor reportingperiodid).Table - PeriodsFields - Reporting Period id intReporting Period desc varchar(30)Reporting Period Begin Date datetimeReporting Period End Date datetimeIf the user selects a 3 then the begin date is Jan. 1, 2004 and theend date is June 30, 2004.Now I need to calculate did any money come in for each week in thattime frame. I need to create a weekly list of all the weeks in thattime frame. Each time frame begins on a Monday. So my list wouldlook like1/5/20041/12/20041/19/20041/26/2004All the way to the end of that time period.How do I create this weekly list from a given time period using T-SQL?I would appreciate any and all help on this.Thanks,Tony
I have a stored procedure that grabs the Region key from the user, and the date. But i want to set up 5 reports, for day of the week. So i want to make the second parameter a defualt parameter set by me in each report. which is better then having to create 5 stored procedures with the same info just different dates. when i try to enter the same data i have in my stored procedure to get Mondays date, i get an error.
Here is what i entered in the report parameter default expression box for the date :
I have a query and am trying to just return the difference between two dates but not include weekends.For instance, if I have 08/21/2006 - 08/28/2006, there are 6 weekdays. I tried this, but I am getting 7 as a result.SELECT DATEDIFF(weekday, request_start_date, request_end_date) AS days_off, request_id FROM request Any help would be greatly appreciated.
In my below T-sql the shipment date is set to next Thursday, if the shipment is missed in current week. Now, I've to change the t-sql code to change the shipment dates to Monday instead of Thursday.
USE tempdb; GO DECLARE @Date DATE; SET @Date = GETDATE(); --SET @Date = '2014-07-25'; DECLARE @TEST_DATA TABLE
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
I have used both data readers and data adapters(with datasets) in the projects that I have worked on. I am trying to get some clarification on when I should be using which one. I think I am doing this correctly but I want to be sure I am developing good habits.
As the name might suggest, it seems like a datareader is for only reading data. I have read that the data adapter and dataset are for a disconnected architecture. Or, that they can be used for this type of set up. I have been using the data adapter and datasets when writing to a database and the datareader when reading from a database.
Is this how these should be used? Is the data reader the best choice for reading data? Am I doing this the optimal way from a performance stand point?
......................................................thanks in advance
We already integrated different client data to MDS with MS Excel plugin, now we want to push back updated or new added record to source database. is it possible do using MDS? Do we have any background sync process to which automatically sync data to and from subscriber and MDS?
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.
The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?
Is there any performance impact if we enable CDC on OLTP db?
Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?
What is the best way to implement CDC to take incremental changes for reporting.
Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
I'm using Script Component to load data into Oracle DB due to the poor performance issue. Now, I found it will missing some data during the transmission. Please see the screenshot below:Â
