Get Date Of Tuesday Of Past X Years From Now
Feb 4, 2015
I am trying to get the date of Tuesday from now of the past x Years
The below Query works fine for weeks
DECLARE @x INT
SET x= 53
SELECT CONVERT(VARCHAR, DATEADD(WW,-@x,DATEADD(DD, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)
The issue is with below query for any passed Number of Years
SET x = 7
CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)
So need to get past years Tuesday Date and also verify if week also works for any passed week numbers ? The x parameter can be changed based on the situation
View 9 Replies
ADVERTISEMENT
Apr 30, 2007
Hi every one,
I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview.
How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.
View 4 Replies
View Related
Aug 26, 2006
How should I find the dates for 3 days past and 5 days future. Such as TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
View 5 Replies
View Related
Aug 26, 2006
How should I find the dates for 3 days past and 5 days future. Such as TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
View 20 Replies
View Related
Mar 27, 2007
I have a table name employee and datetime column named dateofbirth, how can i write a select statement to show their, date of birth and age in years?
This is how i did it, i couldn't finish it, Any feedback would be very thankful.
select name, datebirth, year(dateofbirth)
from employee
View 10 Replies
View Related
Apr 11, 2007
Hello,
I've a table which contains the nest following data:
Date Sales Price
01-01-07 5,00
31-03-07 6,00
16-04-07 5.75
26-04-07 6.25
For example, today is 18-04-07. In my report I want to show the most recent Sales Price, but not the price of next week. I want to see the SalesPrice of 5.75.
How can I get it?
Thx!
View 22 Replies
View Related
Apr 24, 2015
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
View 6 Replies
View Related
Aug 20, 2015
I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.
MC refers to Maintenance.
MCLog data:
carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15
[Code] .....
Here is my Table:
CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));
[code]....
View 6 Replies
View Related
Mar 22, 2006
I have a table in my MS SQL 2000 database called News which has a field caled NewsDate. This is a standard Date field which stores the info in this format: 3/1/2001.
I want to create a query that returns one row for each year that there is a story.
For example, if I had this data...
3/1/2001, 6/27/2003. 9/17/2003, 1/1/2006, 4/5/2006
the query would return this result:
2001
2003
2006
This is the query I've started with:
SELECT DISTINCT NewsDate FROM News ORDER BY NewsDate DESC
What modifier can I apply to the NewsDate field to extract JUST the year from the table? If this were ASP I would try something like Year(Date), but, of course, I can't do that here.
Is this even possible? I've been looking up date functions, but haven't found anything that will work in a select statement. ANY and ALL advice will be greatly appreciated.
View 1 Replies
View Related
Jan 6, 2015
Given this table;
DECLARE @table TABLE (HolidayDate DATE, HolidayName NVARCHAR(50))
INSERT INTO @table
( HolidayDate, HolidayName )
VALUES ('2012-01-01','New Years Day'),
('2012-01-16', 'MLK Day'),
('2012-02-20', 'Presidents Day'),
[code]....
How to get a result set that shows a new column called PreviousHolidayDate with the corresponding holidays last years date?
HolidayDateHolidayNamePreviousHolidayDate
1/1/2012New Years DayNULL
1/16/2012MLK DayNULL
2/20/2012Presidents DayNULL
4/6/2012Good FridayNULL
5/28/2012Memorial DayNULL
[code]....
View 4 Replies
View Related
Sep 30, 2014
What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?
as in
SELECT EffectiveDate
from FL.CEFHistory
I need to return the effective date - 90 days and 1 year from that.
[URL]
View 6 Replies
View Related
Nov 3, 2005
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?
View 1 Replies
View Related
May 27, 2008
Hi all -- I'm trying to create a function that can return the date in a particular month on which the 3rd Tuesday falls. (Or the 2nd Thursday, or the 5th Monday, etc.) I know that my weeks always start on Sundays (and end on Saturdays) but I can't seem to figure out how to accomplish this. I've searched these forums and found threads that talk about how to build a calendar-type table but it is not clear, at least to me, how to use such calendar to find the Xth weekday of the month. Any ideas? Thanks,
Bill
View 2 Replies
View Related
Oct 9, 2007
Is there an option is SQL Server or SSMS to view the past few already executed queries.
I need to check what are all the queries fired on SQL server.
------------------------
I think, therefore I am - Rene Descartes
View 6 Replies
View Related
Mar 30, 2007
Hello all,
Is there a way for me to copy a row, do some modification in a certain row and insert it to the same table as a new row?
Forward with thousand thanks.
Best regards,
Tee Song Yann
View 8 Replies
View Related
Jan 24, 2008
Hello All
Our Collection team has a report that has an error that I'm tring to fix. The Person who created this report just hard coded the current month due as =MonthName(Month(Fields!CurrentDue.value) &" " &year((Fields!CurrentDue.Value).
For the Year 2007 it works fine. But now that the new year has started the report Current Month Due read Dec 2008, but it should be Dec 2007. I thought Of putting in some IIF statements but there has to be a easier way. Is there a function that check the past due and automatically increases the year by one. Say the bill in sent in Nov 2007 past due should be 30 days, Dec 2007. 60 Day should be Jan 2008 and so one.
Last Report Hours
=monthname(month(Fields!LastReportedHours.Value)) & " " & year(Fields!LastReportedHours.Value)
Days Past Due
=Fields!DaysPastDue.Value
Current Month Due
=monthname(month(Fields!CurrentDue.Value)) & " " & year(Fields!CurrentDue.Value)
Current Output
last reported Days Past due Current Month Due
September 2007 75 Days December 2008
Output should be!
last reported Days Past due Current Month Due
September 2007 75 Days December 2007
View 4 Replies
View Related
Nov 20, 2015
I want past two and next two quarters from the present quarterLike now its 4th quarter i should get 3rd and 2nd quarter and next two quarters that is 1st and 2nd also this is my table and when the year changes data is not fetched correctly
View 10 Replies
View Related
Sep 28, 2005
Of database experience do you have?
And what are they?
View 14 Replies
View Related
Apr 20, 2008
If I have and invoice date column and I want to now what invoices are 15 days past due or 30 days past due, how do I do this in a where clause?
WHERE tblInvoices.InvoiceDate ???
View 1 Replies
View Related
Nov 13, 2000
I have a database of the table payments
and I want to create another table with
the olds payments (18 months past)
a have a field with the payment date
date_pay.
How I can do that?
Thanks
Luiz Lucasi
View 2 Replies
View Related
Oct 5, 2006
Hi,
I have a date column and I am trying to build a query that will give me the dates > 48 hours ago (in the past).
For example using todays date 2006.10.05 and time 23:00 I would be looking for the dates prior to 2006.10.03 and time 23:00.
Your help is greatly appreciated!
Thanks!
View 2 Replies
View Related
May 16, 2008
How to find the jobs executed in the past for a perticular period.
e.g. jobs ran between 2-3pm in the past.
I am using sysjobhistory but not really able to use column run_time
------------------------
I think, therefore I am - Rene Descartes
View 2 Replies
View Related
Nov 21, 2007
All,
I am trying to design a graph to shows out build status for past 7-8 weeks. I have to write the SQL query first.
After spending a good chunk of time, I could get some basic chart going. Problem is that I could give it a starting time till now (static) but I am not sure how to get the results for past 7-8 wekks.
1) What is a good approach for this since time is not static and it is changing.
any example or piece of sql syntax is greately appreciated.
Thaks in advance,
aa-
View 6 Replies
View Related
May 25, 2006
Hi,
I have some set of variables that I want to add in all my packages. Like other components, is there any way to copy & paste the variables from one package to another package?
Thx.
View 3 Replies
View Related
Feb 22, 2007
Goodday
Can anyone help please.
I have made a database and need to duplicate the tables to represent new catogaries. The definitions in the tables will stay the same , just the table names will change.
Is there a way to copy and past / duplicate tables without retyping all the definitions each time.
Thanks
Rob
View 1 Replies
View Related
Dec 30, 2004
I just wanted to wish everyone a Happy New Years and I wanted to thank all submitters to this forum for breaking up my code monkey workday and cutting into my productivity and I have found many of these discussions helpful in my preperation for my long procrastinated MCDBA exams.
Get hammered and do not drive.
View 2 Replies
View Related
Dec 19, 2001
--Use the following formula to calculate a person's age in years in a stored procedure,
--where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:
DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)
SELECT @dob = '12/06/1966'
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)
--Notes:
--Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now.
--The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.
--The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement
--the "year" part of the function to calculate the number of year boundaries crossed.
View 20 Replies
View Related
Jul 20, 2005
HI all,This has puzzled me all morning. I have a int field which reads19691124 (UK dates) this is actually a date 24 November 1969 I need toadd 28 years to this making it 19971124 but I’m stumped!Any ideas anyone?CheersSean*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 4 Replies
View Related
Feb 27, 2007
Hello,I would like to show the result fo each day of the past 30 days. I might not have data to display for everyday but I still want the date to show up01/27/2007 resultA01/28/2007 resultB01/29/2007 .....02/26/2007 resultC02/27/2007 resultD
Thanks for your helpArnold
View 3 Replies
View Related
Apr 1, 2004
I would like to show a datagrid where the columns are the last 12 months from whatever today is. I am drawing from what could be a rather large tagle in the DB. The Table looks like this:LogID LogTally LogDate Item
1 2 3/28/03 apples
2 1 3/29/03 apples
3 2 4/01/03 oranges
4 3 4/01/03 apples
5 1 4/01/03 grapes
.......
293 1 3/17/04 oranges
294 1 3/17/04 apples And if someone wants to see a report on apples, they would see something like:3/03 4/03 5/03..................3/04
3 3 - 3I can select one month (this one) like this:SELECT SUM(LogTally) AS Expr1
FROM TABLE
WHERE (MONTH(GETDATE()) = MONTH(LogDate)) AND (YEAR(GETDATE()) = YEAR(LogDate)) AND (item = 'apples')
GROUP BY itembut, I just don't know how to do it for the past 12 months. Do I play with the dates and do 12 seperate SELECTS?
THANK YOU!
View 2 Replies
View Related
Jul 21, 2004
What would my statement look like if I have a column in a table (SoftwareInstall) called InstalledOn which stores a date in shortDateString format and I want to select all the records where that date is <= 30 days previous to today's date. Any ideas?
View 1 Replies
View Related
Jun 23, 2012
I need to show the count that occurred during the previous 30min. I've been told the following query doesn't work.
SELECT COUNT(*) FROM sampledata
WHERE (id = 254 AND sub_id = 731) AND (sampledate >= DATEADD(mi, -30, GETDATE()))
View 5 Replies
View Related
Jun 17, 2008
How do I write a script that will alert me if the tempdb database grows up past 100 MG and stays there?
Jim
View 3 Replies
View Related