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
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.
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.
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?
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));
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.
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?
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,
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.
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
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 ???
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?
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.
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?
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.
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.
--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)
--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.
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!
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
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!
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?
