Date Query Problem Last 8 Days, Different Year From Current
Feb 19, 2008
Hi,
I'm having problems creating a query that brings the results based on last 8 days on date column (column9) and diferrent year from the current one.
If I run the each filter,
Code SnippetYear(Column9) <> Year(GetDate())
and
Code Snippet
(Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 8), 0)) AND (Column9 < DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))
it returns the right values.
So far I've got (it returns no results):
Code Snippet
SELECT Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10, Column11, Column12, Column13, Column14,
Column15, Column16, Column17, Column18, Column19, Column20, Column21, Column22, Column23, Column24, Column25, Column26, Column27,
Column28, Column29, Column30, Column31, Column32, Column33, Column34, Column35, Column36, Column37, Column38, Column39, Column40,
Column41, Column42, Column43, Column44, Column45, Column46, Column47, Column48, Column49, data_anulacao, data_instalacao
FROM Contratos01
WHERE Year(Column9) <> Year(GetDate()) AND (Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 8), 0)) AND (Column9 < DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))
ORDER BY Column1
Please give me your input.
THX.
View 10 Replies
ADVERTISEMENT
Sep 22, 2006
I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.
View 4 Replies
View Related
Dec 3, 2013
I need to associate aggregate gross_revenue with calendar year, but do not have a date field that reflects payment dates, just contract periods a start_date and an end_date. The contract periods are typically 1 or 2 years and can start at any time I.e start_date 6/1/2012, end date 5/31/13. I think by finding the number of days that fall in each calendar year and storing in a temp table, I can create a simple formula to associate revenue to each year.
View 2 Replies
View Related
Mar 31, 2008
I am using oracle 10G DB as back end.I have two date fields in a table.
1)premium_paying_start_date
2)premium_paying_end_date
i have to get the premium_term i.e, the difference between the two dates(premium_paying_end_date-premium_paying_start_date).
The difference should show the year,month and no of days difference.
For example :
premium_paying_start_date : 14-10-1984
premium_paying_end_date : 01-03-2008
Difference should be : 23 Y : 4 M : 15 D (Y = years, M = months , D= days)
So please give me the solution for this.
View 6 Replies
View Related
Jan 12, 2005
Hey all, how do you take the current date and subtract a year from it in a SP?
What I want to do is...
Take the current date when the SP is ran, subtract a year, then if my date field is within that range (higher than the date with the subtracted year) it will continue in the query.
Edit: the answer is..
dateadd(yyyy, -1, getdate()) as Date1
View 6 Replies
View Related
Apr 17, 2008
Hello
I had a requirement to filter records for the current year only... I went back to the user and explained that the logic wasn't sound... eg if somebody did the search early in January then they would probably get no results back. My suggestion was to go back two months if the current date is in January or february.
So, this is what I have.......
Code Snippet
where cs.startdate > case when year(cs.startdate) = year(getdate()) and month(getdate()) > 2 then
convert(char(4),year(getdate()))+'-01-01' else dateadd(month,-2,getdate()) end
Is there a neater/better way of coding this?
Jon
View 5 Replies
View Related
Sep 21, 2015
I have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX)
@sql = ' insert into target
select from table_1_currentdate a
LEFT JOIN Table_2_currentdate-1 b
on a.col1=b.col1 where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
View 3 Replies
View Related
Oct 22, 2007
How to get current year from SQL query?
View 10 Replies
View Related
Jun 4, 2015
I am trying to use one dataset rather than two and was hoping to then filter the data via a table or matrix.
This is my dataset
SELECT
Practice.ibvStaffCategorisation.StaffId
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableAmount) AS Sum_ChargeableAmount
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableAmount) AS Sum_NonChargeableAmount
,Practice.ibvStaffTotalsCL2Y.Period
[code]....
I would like to display two rows of data for each StaffId one representing the current period and the other all periods to date so the table would look something like below.
StaffId | Non Chargeable Time | Chargeable Time
CJJ | 0:20 | 4:20
| 4:50 | 19:20
JN | 0:05 | 5:30
| 1:30 | 25:30
The above shows two separate StaffId figures the first line for each shows non chargeable and chargeable for the current period and the second line a total of all periods in that year.
I have managed to get the first row to display only figures from the current period by using a filter however it also applies the same filter to the second row in the group. I have also tried to group the rows but am drawing a blank.
View 6 Replies
View Related
Jan 18, 2007
I am writing a usage query, I need to determine what the current month and year is. If the current month is 1, then I need Year-1. I keep getting this error Line 5: Incorrect syntax near '='.
If I comment out the case statement just evaluate the (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))or (YEAR(User_Date) = YEAR(GETDATE())) by itself, the query works. What I am missing? Anybody?1 SELECT CONVERT(char(10), User_Date, 101) AS userdate, COUNT(*) AS CNT, User_Name2 FROM Users3 WHERE (User_Name = 'Joe Bob') AND (MONTH(User_Date) = MONTH(DATEADD(mm, - 1, GETDATE()))) AND 4 case MONTH(User_Date) when 1 then 5 (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))6 else (YEAR(User_Date) = YEAR(GETDATE()))7 END8 GROUP BY CONVERT(char(10), User_Date, 101), User_Name9 ORDER BY CONVERT(char(10), User_Date, 101), User_Name
View 5 Replies
View Related
Jul 20, 2005
Does anyone have an example of an SQL query which returns rows for theyear-to-date, but where the "year" commences on August 1st?e.g. select * from mytable where datefield > last august 1stTIA for any helpIsabel
View 2 Replies
View Related
Jun 29, 2015
How to show the CurrentMonthanddateandyear in my report header in ssrs?
1.How to show the currentdateandMonthyear exmple date format like June 29 2015 on my report header.
2.How to change the report rdl name with the same name like EmpUpdatedreportJune 29 2015.rdl ,it is possible to create and change the rdl file name with the current dateandmonth.
View 9 Replies
View Related
Sep 18, 2013
I have the following script that calculates Sales by month and current year.
We run a Fiscal year from April 1st thru March 31st.
So April 2012 sales are considered Fiscal Year 2013.
Is there a way I can alter this script to get Fiscal Year Totals?
select ClassificationId, YEAR(inv_dt) as Year, cus_no,
isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr',
isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May',
isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun',
isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',
[Code] ....
Data returned looks like the following.
ClassificationID Year Cus_no Apr May June ....
100 2012 100 $23 $30 $400
100 2013 100 $40 $45 $600
What I would need is anything greater than or equal to April to show in the next years row.
View 2 Replies
View Related
Mar 15, 2006
HiI want to write a function that can return a sum for a given daterange. The same function should be able to return the sum for the sameperiod year before.Let me give an example:The Table LedgerTrans consist among other of the follwing fieldsAccountNum (Varchar)TransdateAmountMST (Real)The sample data could be1111, 01-01-2005, 100 USD1111, 18-01-2005, 125 USD1111, 15-03-2005, 50 USD1111,27-06-2005, 500 USD1111,02-01-2006, 250 USD1111,23-02-2006,12 USDIf the current day is 16. march 2006 I would like to have a functionwhich called twice could retrive the values.Previus period (for TransDate >= 01-01-2005 AND TransDate <=16-03-2005) = 275 USDCurrent period (for TransDate >= 01-01-2006 AND TransDate <=16-03-2006) = 262 USDThe function should be called with the AccountNum and current date(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.How can I create a function that dynamically can do this ?I have tried f.ex. calling the function with@ThisYear as GetDate()SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +'-'+datepart(y,@ThisYear)But the value for @dateStart is something like 12-07-1905 so thisdon't work.I Would appreciate any help on this.BR / Jan
View 3 Replies
View Related
Oct 20, 2007
Guys,
I wanted to find the ratio: (sales made for current year 2007 - sales made for previous year 2006)/sales made for previous year 2006.
so, the result should be something like this:
Year: Sales: %change in sales:
2005 100 10%
2006 200 20%
2007 300 30%
How do I write a query for this...??? so that i can plot this in a chart in SSRS.
somebody help me.
View 4 Replies
View Related
Sep 29, 2015
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
View 3 Replies
View Related
May 25, 2015
Our business get orders through the week with the weekends (Fri & Sat) orders being higher than weekdays. Im wanting to graph this years data with last years and possible the years before but to compare days in such a way that the all the weekdays line up. so comparing 2015 week 1 with 2014 week 1 but with 03/01/2015 (Sat) lining up with 04/01/2014 (Sat) etc.
I'm looking for alternatives to adding or removing days from the dates to solve this issue, i have a date dimension table for the past 5 years that i can use to compare calendar week 201401 with calendar week 201501 but I am finding it a bit inflexable.
View 5 Replies
View Related
Jun 18, 2008
Hi Guys,
I currently have a large database, and I wish to query it.
It is running MSSQl 2000.
I wish to have a query that when ran, returns results between NOW() and 7 days ago (A weekly report from the current date).
The date/time field I am sorting by is stored as YY-MM-DD 00:00:00
Any help is appreciated :)
View 9 Replies
View Related
Mar 12, 2007
Hi,
I've searched quite a bit for help with this syntax but have given up.
I need help with the where clause of a query using SQL SERVER that selects records a certain number of days before the current date. I have tried this and it's incorrect syntax:
WHERE (fldDate < ({ fn NOW() - 500 })
Can someone please help me out with correct syntax for this? thanks much.
View 3 Replies
View Related
Jan 16, 2008
Hi,
I'm trying to query an SQL table column with date values to show 8 Days ago results.
I've started with this query:
SELECT ficheiro, erro, descritivo_erro, contrato, DO, movimento, data, descritivo, tipo_movimento, desconto, montante, comissao, IVA
FROM status_day
WHERE (YEAR(data) = YEAR(GETDATE())) AND (MONTH(data) = MONTH(GETDATE())) AND (DAY(data) = DAY(GETDATE()) -8)
ORDER BY descritivo_erro, contrato
The problem is that the text in red will have some problems when the month changes - If I want the 8 days ago results from January and the system date is 1st of February the query will not return any values.
I read something about DATESERIAL but is wasn't conclusive on how to use it with system date.
Please help me out with this query.
View 6 Replies
View Related
Jun 20, 2008
Dear gurus..,
I need to get all the days in a year in a single query.
Thanks in advance.,
cool...,
View 9 Replies
View Related
Mar 18, 2015
This is what I have for a query but i would like to take the year off. is there a way i can just have the month and day without the year attached?
View 3 Replies
View Related
Jan 18, 2007
Hi,I have a simple table called events, which lists the start and end dates of events. I'm using a calendar control that queries the db for events, but at the moment it does a check for every day by passing in the day. (Very inefficient) What I'd like to do is pass in the current month, and get a set of rows that have an event which is in that month. It sounds easy, but I'm a little confused about what to do if the event starts current month -1 and ends currentmonth +1 I obviously need to return results like these also.
View 4 Replies
View Related
Mar 11, 2008
Hi, I've just started using SQL Server Express with VWDE 2005 and I have a database with one table called EVENTS which has a datetime column called DATE. I want to select records where the DATE value is in the next two months.First I tried selecting records where the DATE value is >= today. I used the query builder to produce... SELECT [Date], [Title] FROM [Events] WHERE [Date] >= GETDATE()but got the error "Undefined function 'GETDATE' in expression".Am I missing something obvious?
View 5 Replies
View Related
Oct 11, 2007
Hello
I'm trying to figure out how to write this query...for example,
A stud attended University of Toronto in 2001 and then attended Seneca College in 2006 ..... I want to select the Education Institute that the stud attended last.
Thank you
View 1 Replies
View Related
May 7, 2015
How to get the result for current date orders but I can get the query working for due date.
"
SalesOrder.DateEntered,
SalesLine.OrderQuantity, (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) AS Value,
(SalesLine.SellingPrice / SalesOrder.ExchangeRate) AS Price
FROM Company INNER JOIN
CustomerAccount ON Company.CompanyID = CustomerAccount.CompanyID INNER JOIN
SalesOrder ON CustomerAccount.CustomerAccountID = SalesOrder.CustomerAccountID INNER JOIN
SalesLine ON SalesOrder.SalesOrderID = SalesLine.SalesOrderID
WHERE (SalesOrder.DateEntered = { fn CURDATE() })
"
How could I get the current date orders?
View 9 Replies
View Related
Feb 12, 2015
select DateAdd(dd,-90,getdate())
It gives the 90 days older date but i want to exclude the weekdays and holidays (saturday ,sunday,holiday)
As we dont have permission to call a function or Sp in high level environments looking for a simple query .
View 1 Replies
View Related
Jun 16, 2007
Hi there, is that any function in ms sql server 2000 where if i pass a date or a year then it could gives me the total days of year from the parameter?
in mySQL got select DAYOFYEAR(date);
can some one guide me on this please...i need to use it for a leap year function for my SP!
View 12 Replies
View Related
Aug 30, 2004
I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:
CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()
And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?
I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?
Thank you,
Cesar
View 4 Replies
View Related
Aug 13, 2007
HELP... I’m new to ASP.NET so please
excuse my inexperienced question. I’m using VWD 2005 and a remote SQL 2000.In the (Configure Data Source) under the (Select) tab I have
this simple query:
Select * from calendarWhere event_date = now
Right off the that bat you can probably see the problem. I
just want to retrieve all event at is equal to today (current date). In classic
ASP is can use <%now%> and in ColdFusion I can use #now()#. What is the
proper syntax the get the server current date and time, like (getDate()) in SQL?
Thank you in advanced for you help!
View 6 Replies
View Related
Sep 17, 2007
Hi,
I'm wanting to generate a list of all the days that fall between a certain period of time (month, year etc.), but can't find any documentation on how to do this, does anybody know the sql query to do so?
TIA!
View 2 Replies
View Related
Dec 26, 2013
I come in to work 6.30am, and need to audit what happened from 5pm when I left to 6.30am this morning. I have used code to search 13.5 hours back from any given time:
SELECT * FROM TRANSACTION_HISTORY
WHERE TRANSACTION_HISTORY.ACTIVITY_DATE_TIME > (SELECT DATEADD(hour,-13.5,(SELECT MAX (TRANSACTION_HISTORY.ACTIVITY_DATE_TIME) FROM TRANSACTION_HISTORY)))
Problem is if I run query later, I lose time from the start, eg. If I run query at 7am, I only get results from 5.30pm onwards. Rather than change criteria every day, I wanted to able to search from 6.30am of the current day, back to 5.30pm of the previous day.
View 3 Replies
View Related
Aug 13, 2004
Hi everyone,
Currenly, I'm getting the current year in my code, and passing this to my stored procedure.
I'm sure there's a way just to get the current year (ie '2004') just by using SQL.
I was just about to post this question here when I did a bit more digging around and found my solution:
DATEPART(yyyy, GETDATE())
This will get the year.
Hope this helps someone searching for the answer!
View 2 Replies
View Related