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

How Can I Retrieving Records By Past One Week,past Month, Past Quarter Of Year And Then Half Year.

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

Past And Future Date

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

Past And Future Date

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

How To Get Date Of Birth And Age In Years?

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

Find The Most Recent Date Of The Past

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

SQL Server 2012 :: Sales Over Years - Retrieve Values In Single Query For Multiple Years And Grand Total?

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

Transact SQL :: Last Record Not In Past 9 Months From Todays Date?

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

SELECT DISTINCT To Return Only The YEARS In A Date Field?

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

SQL Server 2012 :: Get Previous Years Holiday Date?

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

T-SQL (SS2K8) :: Calculate 90 Days And 3 Years Ago From Effective Date In A Table?

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

How To Convert Dw Into Monday, Tuesday, Etc

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

Finding The 3rd Tuesday Of The Month

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

Past Queries

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

Copy And Past A Row

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

Past Due Report

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

Past Two And Next Two Quarters

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

How Many Years

Sep 28, 2005

Of database experience do you have?

And what are they?

View 14 Replies View Related

How Can I Calculate The Days Past Due

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

How Calculate 18 Months Past ?

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

Trying To Obtain Past Dates

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

Jobs Executed In The Past

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

How To Get Data For Past 7 Weeks

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

How To Copy && Past Variables

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

Copy And Past Tables

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

Happy New Years

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

Calculating Age In Years

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

Add 28 Years Aagin

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

How To Show Result For Each Day Of The Past 30 Days?

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

Generating A Table From A Log For Past 12 Months

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

Select Records From Past 30 Days

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

How To Pull Records In Past 30 Mins Only

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

Alert For Tempdb Growing Past 100 MG

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







Copyrights 2005-15 www.BigResource.com, All rights reserved