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
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
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.
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.
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.
Hi all,
I need help writing a query that will subtract the values of 2 rows from the same column to display in the result set. Some background information: a table has a sales column that keeps track of sales by the minute, but this is done in a cumulative manner, i.e, sales at row 3(minute 3) = sales recorded @ minute 2 plus sales @ minute 3. Therefor to get the actual sale at min 3, i would have subtract value at row 2 from row 3. make sense? it sounds very easy but I am having a hard time refering back to the previous row and am dealing with more than 1000 rows. i thought about doing a self join on the table but could not get it to do what i want.
would appreciate any help i can get. thanks
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.
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 RelatedGuys,
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.
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.
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
I want to fetch all the records which are 2 minutes older
How can I do it?
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 RelatedHi 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!
Hello,
I'm trying to write a function which will give me, among other things, the current year.
I'm brand new to SQL so don't really know how to use functions properly yet, although from my VB knowledge, the structure looks very similar.
I've tried SELECT GETDATE() but it gives me an error about getdate not being valid inside a function.
How can I get this to work please ?
Cheers,
J.
I have a table like this:
Bottom Top
0 2
4 6.5
9 14
15 17
In Transact SQL I need to calculate the difference between the current bottom and the previous top. If there is no previous top, then the result would be the current bottom.
So, the result would be 0, 2, 2.5, 1
I must return the value in a table. Thank you all for your help!
Karen
Hi,
I'm making some sort of application where people can add their resume.
They also need something to add places where they have worked or currently are working.
I have a form where they can add this and i add this experience using the following stored procedure:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_Insert_Experience]
@ExperienceId uniqueidentifier,
@ExperienceEmployee nvarchar(100),
@ExperienceFrom datetime,
@ExperienceUntil datetime,
@ExperienceQualifications nvarchar(250),
@ExperienceTechnologies nvarchar(250),
@ExperienceTasks nvarchar(250)
as
insert into Experiences
values(@ExperienceId,@ExperienceEmployee,@ExperienceFrom,@ExperienceUntil,@ExperienceQualifications,
@ExperienceTechnologies,@ExperienceTasks);
It must be possible to add the place where they currently are working. Then the ExperienceUntil has to be something like still going on. Then I decided that the user then had to set the current date.
But what I want is the following, I want that the ExperienceUntil keeps updating automatically, like everytime i get that record, it has to have current date.
Is this possible ?
But if the ExperienceUntil isn't the current date , it just had to take the supplied parameter date.
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.
I have a field in my database that holds a date, the only part of the date I care about is the month and day (12/2/). I'm trying to use this field in a view column to show the for example 12/2/ and the current year (2007). Also if the month and day have passed like 11/1/ then it would be next year (2008).
Can anyone help me with this?
I want an sql query that wil give me listing of 20 years from current year.
View 2 Replies View Relatedhow to insert current year into table,
I tried ,
insert into tablename values('','',year)
i also need to know how to insert month , day
what is the query to find
current year
current month
current day
using sql query analyser
How to get current year from SQL query?
View 10 Replies View RelatedWe are running sql 2008 R2. We have the JE_DATE field set up as an int. We are trying to subtract 30 days from this date field.
select * from GENERAL_LEDGER
where JE_DATE >= DATEADD(DAY,-30,GETDATE())
In addition to the where clause above we have also tried
where JE_DATE >= DATEADD(dd,-30,GETDATE())
For both we get the following error "Arithmetic overflow error converting expression to data type datetime."
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
I have the following working correctly as a trigger, however, I want to change one of the values (2016) to calculate the current year's value.. so in 2017, it would put 2017.
CREATE TRIGGER TRG_NEW_EQUIPMENT
ON ATHLETE AFTER INSERT
AS
BEGIN
INSERT INTO Equipment (Equipment_ID, Equipment_Model, Equipment_Year, Equipment_Brand, Equipment_Color, Equipment_Condition_Rating)
VALUES ('150','Big Spin','2016','K2','Blue','5')
END;
GO
Hi,
I have a Serial No which has a length as 14.For eg IL010730123456. IL01 is The Default Code. 07 is the current year and 30 is the week of the year. 123456 is Serial. How shoud I find The Year and week with the help of this serial no ie The First day of the 30th week of 2007.Is it Possible?
Plz Help me with an appropriate solution.
Thanks.....
I would like to default my first parameter, which is year, to the current year. I'm querying against a cube. I've tried setting the default =Non-queried =year(today), tried a few MDX field names, and also tried setting the current year as the default in my data set. I can't seem to get the parameter to accept a default.
View 3 Replies View RelatedI have a list of patient encounter dates ordered by the date. I need to subtract the previous date in order to get the number of days between each date for the same patient.
create table TEST
(
MRN varchar(10),
EncDTTM datetime,
Sequence int
)
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-01-24','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-02-03','2')
[code]....
I have a user defined function in datebase SQL 2000. function looks like
create function Getcurrentdate(@month int, @day int) returns smalldatetimebegin
declare date1 as smalldatetime--get current year --convert month, day and year into current date. then return
return date1end
my problem was , after using getDate(). I get error meassage which is "can'not use getDate() inside user function"How can I get current year in the user defined function. Thanks
I am working on some payroll related code which currently has the following hard-coded CASE statement (I won't include the entire thing, it is lengthy):
AND b.TCDateTime BETWEEN '2013-01-01 0:00' and '2013-12-31 23:59'
I want to get rid of the hard coding, and have this use current year dates. So for 2014, it should reference rows where the TCDateTime is >='2014-01-01 0:00' AND <'2015-01-01 0:00'..I think the following would accomplish this, but would like confirmation that this is the 'best' way (and that it will work!)
SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy, GETDATE())) + '0101') AS curryrbegin
--output should be yyyy-01-01 00:00:00.0 where yyyy is current year
SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy + 1, GETDATE()))
+ '0101') AS nextyrbegin
--output s/b nextyear-01-01-00:00:00.0
Then, change the CASE statement to read:
AND (b.TCDateTime >= curryrbegin AND < nextyrbegin)
I have some my below requirment to loading some last year and currnet year records for some ID's in my table,
We have to load the ID's that are active at the end of the year for the prior year and ID's that are active as of today for the current year.Here is the scenario when the ID is currently terminated but active at the end of the prior year and the record is not in the table.so, we didn’t load the count for the prior year
Here prior year is 2015-2015 and Current year is 2015-2016
CREATE TABLE remp_year
(ID INT,
STATUS NVARCHAR(100) NULL,
START_DATE DATE NULL,
END_DATE DATE NULL,
date_year nvarchar(10) NULL)INSERT INTO remp_year VALUES (10,'Active','2015-05-26','2015-12-31','2015-2016');
[Code] ...
Here ID 20 and 50 for terminated records is the prior year records so it should count for the last year and those are active in this year those will count for this year.
I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77
I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
lack of idea to put the TIME condition.
Plz help me..