How To Find Second Monday Of October?
Jul 2, 2006Hi,
Can any one help to get the date of second monday of october for the given year.
Ex: the input will be 10, 2006
and output should be 9, where 9 is the second monday date
ADVERTISEMENT
Put All The Day Monday'S Next Month + Add This Last Monday ?
Mar 5, 2008i 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
*/
------------------------------
TNX
Off Topic: Paul OOF Until 2nd October
Sep 14, 2006I'm in China doing TechEds in Shanghai, Guangzhou, Beijing and Hong Kong - returning to the office Monday 2nd October.
There may be significant delays in my replies to any topics on this forum. Just fyi so you don't sit waiting for me to respond.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
DB Engine :: October Dates Losing Hour On Table Insert
May 5, 2015I have a datetime field used to store a date of birth. When inserting the date of birth into the table it works fine. But when the date of birth is in the month of October, it takes an hour off. For example, if the date is 04-OCT-1993, it inserts as 03-OCT-1993 23:00.
It only happens for dates in October. It's being inserted via a .Net table adapter. It just so happens that October is the month that daylight savings kicks in parts of Australia (an hour is added), but I think this must be a coincidence.
How To Convert Dw Into Monday, Tuesday, Etc
Nov 3, 2005I 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?
View 1 Replies View RelatedHow To Put All The Day Monday'S Next Month In One Table
Jan 10, 2008hi need help
how can i put all the monday'S day next month in one table like this
so i can use it all years
date index number
---------------------------------------------------------
04/02/2008 first_monday 1
11/02/2008 second_monday 2
18/02/2007 third_monday 3
25/02/2008 fourth_monday 4
TNX
Can I Easily SELECT A Date As October 12, 2012 (instead Of Oct 12 2012)?
Jan 29, 2008Presently using CONVERT(VARCHAR(11), [ExpiryDate], 100) to get close to the correct format.Only stumbled across this by accident so am assuming there might be better hidden 'treasures' .Formatting dates seems to be my biggest time-consuming activity and I just don't seem to get better at it!
View 4 Replies View RelatedGet Last Monday Through Sunday Data Question
Jan 12, 2007I'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 - -
any ideas?
T-Sql - Calculate Each Monday For A Given Time Period. Help??!!
Jul 20, 2005I 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
View 3 Replies View RelatedLast Monday Date As Default Parameter Value
Dec 3, 2007
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 :
=DATEADD(dd, - 6, DATEADD(dd, 1 - DATEPART(w, GETDATE()), CONVERT(varchar, GETDATE(),
101))))
but i keep getting an error that says:
The Value expression for the report parameter €˜Daily_activity_statistics_datetime€™ contains an error: [BC30451] Name 'dd' is not declared.
Build complete -- 1 errors, 0 warnings
whats wrong with it? This works fine in the stored procedure for the where clause. Please help! this would save me alot of time.
DATEDIFF Return Monday - Friday Or Just Weekdays
Aug 30, 2006I 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.
T-SQL (SS2K8) :: Set Shipment Date To Monday Instead Of Thursday?
Oct 27, 2014In 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
[code]...
Query To Show Time Monday To Sunday?
Jul 17, 2014I 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
Set Default Parameter Value Monday Through Friday Of Last Week.
Dec 6, 2007
I have a weekly report that i want to display mon tues wed thur and fridays date from last week.
I know how to specifically pick one date:
=DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))
that would set the parameter to monday. But how to do i make it select all the dates from monday-friday of the previous week?
SQL Server 2008 :: How To Retrieve Only Monday Dates From Each Month
Jan 23, 2015How to retrieve only Monday dates from each month in sql server.
If i pass any value (Let say GETDATE()) then i should get all monday values from the current month.
I want to calculate bi weekly range data in the sql.
Transact SQL :: How To Generate Week Ranges From Monday To Sunday
May 28, 2015I 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
Monday To Sunday - No Data For A Sunday Being Displayed
Sep 15, 2013I 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)
Transact SQL :: How To Get Date - Last Monday From Given Date
Jun 15, 2015How can I get a date that would be last/previous Monday from given date?
For example for given 22/01/2015 it would give 19/01/2015.
How To Get Starting Datetime(monday) Of The Week And Ending Datetime Of The Week(sunday)
May 2, 2007hi friends,
how to get the date of the first/last day of the week in sql...
can any one help me
Cheers,
raj
Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?
Jul 26, 2005I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedProblem: Find All Table Names In Db And Then Find
Jun 12, 2008I have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
To Find Www.
Mar 6, 2001Hello!
Please help me to find web page where guys who passed MS exams share their experience. I remember it should be like BenyenVine or very close...
Thank you,
Elena
Using Contains() To Find -5
Jun 12, 2007Everytime I search for this text, it seems to pick up stuff beginning with 5, not dash 5. Does something need to be escaped and how would this be done?
View 8 Replies View RelatedCan't Find My UDF
Mar 2, 2006Sometimes the simplest things are the most difficult... I'm creating a
UDF as below, then executing it but all I get is that the object does not exist. I must be missing something very basic here...
CREATE function dbo.GetColumnLength(@vcTableName varchar(50), @vcColumnName varchar(50)) returns smallint
as
begin
declare @intLength as smallint
select @intLength=sysC.prec from syscolumns sysC, sysobjects sysO
where sysC.Id = sysO.Id AND sysO.xtype ='U' And
sysO.Name = @vcTableName AND
sysC.Name = @vcColumnName
return @intLength
End
GO
select top 2 * from player, dbo.GetColumnLength('playerdetails','email')
Where Can I Find....
Oct 23, 2007Some pre-written scripts that contain things such as automobile make or other generic common data like that?
I'm trying to avoid populating my db by hand. Any insight would be appreciated.
cheers
Find
Jul 20, 2005Can someone help on this:I am just learning, and I'm connecting to the the northwindcs.mdf tables /open file is northwindcs.adp.This is the sample installed using msde, which is supposed to be mini sqlserver to learn.Please don't refer me elsewhere, here is what I'm trying to learn:If I want to hit a command button and do the following:1. Find a customerid2. if found, edit the record, if not found, add a new record.How would the below code need to look for this, I'm not even sure theconnection string is correct.I'm getting following error:run-time error 3219operation not allowed in this context.I get the y messagebox, but rst!ContactTitle = "The Owner" doesn't work.When I hit the debug, rst.close is highlighted.Also, how do you handle a no find situation here, I noticed a nomatchdoesn't work.I am real good at programming, but new to the server thing.And finally, is there a way to hit this command button, and do all from astored procedure instead of code? But in background, no user inteventiononce button is hit. Which is better, this code approach or a possiblestored procedure.Please help, if I get this down, I think I'll have the rest wipped. Theconnect string is one big thing confusing me along with handling record oncefound / not found. I'm used of DAO. If some one is willing to help, I canemail detailed real code from a database I'm really working on. I need tolearn this first to convert code.HERE IS SAMPLE CODEPrivate Sub Command16_Click()Dim cnn As New ADODB.ConnectionDim rst As New ADODB.RecordsetDim mark As VariantDim count As Integercount = 0cnn.Open "DSN=NorthwindCS; Provider=SQLOLEDB;Data Source=OEMCOMPUTER;InitialCatalog=NorthwindCS; uid=sa; pwd=;"rst.Open "SELECT * FROM Customers", cnn, _adOpenDynamic, adLockOptimistic, adCmdText'rst.Open "SELECT CustomerID FROM Customers", cnn, _' adOpenDynamic, adLockReadOnly, adCmdText' The default parameters are sufficient to search forward' through a Recordset.rst.Find "CustomerID = 'CHOPS'"If rst!CustomerID = "CHOPS" ThenMsgBox "y"rst!ContactTitle = "The Owner"ElseMsgBox "n"End If' Skip the current record to avoid finding the same row repeatedly.' The bookmark is redundant because Find searches from the current' position.'Do While rst.EOF <> True 'Continue if last find succeeded.' Debug.Print "Title ID: "; rst!CustomerIDcount = count + 1 'Count the last title found.'mark = rst.Bookmark 'Note current position.'rst.Find "CustomerID = 'CHOPS'", 1, adSearchForward, mark'Exit Do'Looprst.Closecnn.CloseDebug.Print "The number of business titles is " & countEnd Sub
View 8 Replies View RelatedFind And Replace
Sep 13, 2006To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.
View 1 Replies View RelatedSql String Find With &&
Jun 22, 2007I have a visual studio 2005 app that has to match strings to something in a database.
It works wonderfully until a string shows up with an & in it (so "this & that")
The app wont find anything even though it should, the same query that is made in VS works just fine in enterprise manager
I tried a replace on the & with the chr(), hex, and html reference for & and none of those work.
We tried using different methods in VS to do the sql statement and commands.
The statement surrounds the string with single quotes.
If we take the & out, everything works fine.
Turned off request valadation just in case.
Any ideas?????
SQL Find Question
Feb 26, 2008I am a novice....
Where would I start, if I wanted to search my SQL database for a record and have SQL return a true/false. I want this to be done with ASP.net.
How To Use WHERE To Find Various Matches
Apr 26, 2008Hello.I have a select that returns some SubCategories.SELECT SubCategoryID from SubCategories SCATINNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryIdWHERE SCAT.ParentCategoryId = X Now i will to retrieve all rows from a Table called Items Where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.What code i need to write toI think .. Select * from Items AS IT WHERE IT.Subcategory = ???I don't know, anyone can help meThanks
View 4 Replies View RelatedFind And Replace.
Apr 30, 2004Does anybody know how to search through all stored procedures (in SQL Server 2000) and find and replace text without having to open each one individually in Query Analyzer and doing so one at a time?
That would be so time consuming. I want to be able to change my table names, but I have so many stored procedures allready using the old names. To go and find each name and replacing them is a task I don't want to even try and do.
Thank you to whomever can help.
Alec
How To Find An MS SQL Server DBA
Sep 1, 2004My ISP runs MS SQL server for me and does not have experience managing this type of database. For example, the database occassionally crashes and looses data. Our site is not live yet, but once we go live, this is unacceptable.
I need somebody to consult on the database configuration, tuning, security and maintenance plan. How would I find a person with the correct qualifications?
Can't Find SQL Server
Feb 13, 2006I have the following in my web.config<appSettings> <add key="strConn" value="server=10.100.1.2;uid=sa;pwd=;database=MyDB/></appSettings>and I am getting the following error message --"SQL Server cannot be found or access denied"if I replace the server IP address with server name, it works fine. Am I missing something here? Help!ThanksBugme
View 1 Replies View Related