T-SQL (SS2K8) :: DATEADD And Leap Years
May 29, 2012
DECLARE @MyDate datetime
SET @MyDate = '2/28/2009'
SELECT DATEADD(year,-1,@MyDate)
Desired result '2/29/2008'
This below seems to work. (Subtract a year and then find the last day of the month for that date, set to midnight)
SELECT DATEADD(dd, DATEDIFF(dd,0,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(year,-1,@MyDate))+1,0))), 0)
Is this a reasonable approach? I thought the SQL Server data and time functions were aware of leap year?
View 9 Replies
ADVERTISEMENT
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
Jun 29, 2015
I have a data set (snippet below) and I need to count the number of consecutive years based on a date in time for each ID as represented below.
ID DATE
------ --------
1 2000-05-03
1 2001-06-10
1 2002-04-02
1 2005-07-29
1 2010-12-15
4 2001-05-07
4 1999-08-01
4 2000-07-05
4 2001-08-01
9 2002-05-01
9 2000-04-02
My result set needs to be something like:
ID Count of Consecutive Years
------- -----------------------------
1 2
4 2
9 0
I know this is a gaps and islands type problem but nothing I have been able to find is working once I attempt modification so that it can fit my dataset. Please note that I am going to use the data return to populate another table that is currently being populated using a cursor that utilizes an insert statement based on different codes.
View 9 Replies
View Related
Jul 10, 2015
Ok, I'm looking to get counts on historical data where the number of records exists between two dates with different years. The trick is the that the dates fall in different years. Ex: Give me the number of records that are dated between 0ct 1, 2013 and July 1, 2014.
A previous post of mine was similar where I needed to get records after a specific date. The solution provided for that one was the following. This let me get any records that occured after May 1 per given Fiscal year.
SELECT
MAX(CASE WHEN DateFY = 2010 THEN Yr_Count ELSE 0 END) AS [FY10],
MAX(CASE WHEN DateFY = 2010 THEN May_Count ELSE 0 END) AS [May+10],
MAX(CASE WHEN DateFY = 2011 THEN Yr_Count ELSE 0 END) AS [FY11],
MAX(CASE WHEN DateFY = 2011 THEN May_Count ELSE 0 END) AS [May+11],
MAX(CASE WHEN DateFY = 2012 THEN Yr_Count ELSE 0 END) AS [FY12],
[Code] ....
I basically need to have CASE WHEN MONTH(OccuranceDate) between Oct 1 (beginning year) and July 1 (ending year).
View 4 Replies
View Related
Feb 28, 2008
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev
FROM dbo.EntityDef INNER JOIN
dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN
dbo.CurrentDate INNER JOIN
dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate
WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')
ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate
I appreciate all your help
Thanks
View 13 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 17, 2006
I just realized DBTIMESTAMP does not support leap years...Has anybody experienced this? If so, how did you get around it? Obviously, there are multiple ways; just curious how others are handling it.
View 21 Replies
View Related
Dec 16, 1999
Run the following code for a nice error message!
declare @datedatetime
select @date = '2/29/2000'
select DATEADD(day, 1, Convert(char(8),@date))
Is there a better solution than to remediate the code with an IF Statement?
View 4 Replies
View Related
Feb 28, 2008
Hi,
I am using SSRS interface to create and schedule a report.
The report parameters are as below...
Start Date: 01/03/2008 (dd/mm/yyyy)
End Date: 12/12/2009 (dd/mm/yyyy)
Schedule Details: Monthly (Feb) on 29th
Which means the report should not execute as 29th Feb will not appear on 2009. This date will come only in 2012 which is a leap year.
What SSRS does is, it creates a report and schedules it on 29th March 2009 (29/03/2009)
Which is wrong.
We have tried this issue several times with different date combination and the result was same.
Did any one come accross this type if issue? If yes please let me know the solution.
Is this a BUG of SSRS???
With regards
Anirban
View 4 Replies
View Related
Sep 28, 2005
Of database experience do you have?
And what are they?
View 14 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
Dec 30, 2005
remember if you drink too much, get a taxi and charge it to your company.
View 4 Replies
View Related
Oct 24, 2006
Hi there.
I work for a charitable organization, am new to this form (and sql programming) and trying to create a flag for unique records indicating the number of consecutive years a donor has given.
I have create a sample db idenifying donor, giving year and total pledges with multiple donor records existing for multiple years having donated.
CREATE TABLE mygifts06 (Donor_id varchar (10), Gift_yr nvarchar (4), Tot_pledges numeric (16,2))
INSERT INTO mygifts06 (Id,Gift_yr,Pledges)
SELECT 155758,2005,15.00 UNION ALL
SELECT 155759,2004,25.00 UNION ALL
SELECT 155758,2004,40.00 UNION ALL
SELECT 155757,2005,100.00 UNION ALL
SELECT 155758,2002,30.00 UNION ALL
SELECT 155758,2001,120.00 UNION ALL
SELECT 155755,2003,15.00 UNION ALL
SELECT 155758,2006,80.00 UNION ALL
SELECT 155757,2003,65.00 UNION ALL
SELECT 155759,2005,400.00
For the above dataset, I am trying to create the following output
Donor_id 2_consec_gifts 3_consec_gifts 4 consec_gifts
--------- -------------- -------------- --------------
155755000
155757000
155758110
155759100
Do I need to use a cursor for this task? I lack experienced in using cursors is there an alternative method someone could suggest?
Thanks in advance.
View 9 Replies
View Related
Jun 23, 2008
SELECT * FROM TEST NOLOCK
WHERE FY_CD = DATEPART(YEAR, GETDATE())
This gives me last years - but they want last years 2007 and 2008
Any suggestions
View 3 Replies
View Related
Sep 30, 2014
I want an sql query that wil give me listing of 20 years from current year.
View 2 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
May 21, 2008
Hello to everybody, I'm quite new to the Analysis Services world and would like to create a BI Solution for one of my customers with the following problem: I have sales data from the ERP from different years (2005 and on). My customer wants a report that shows the sales of the current year up to the month he wants to specify (e.g. january - march 2008) compared with the sales of the same period of the previous year (e.g. january - march 2007), divided into product category.
Is there a way to build a cube and then a matrix report in Reporting Services that lets the user specify the period for the current year and shows the same period of the previous year. Do I achieve this behaviour with a calculated member in the cube (e.g. ParallelPeriod)
Any hint (or web like example) would be very appreciated.
Best regards
Alessandro
View 1 Replies
View Related
Nov 8, 2007
Hi and thank you in advance to whomever takes the time to read this entry. I will be as detailed as possible, so I apologize for the length. The information here relates to the automotive industry, but I don't believe that is a very important detail
The overall purpose of this query is to create a compressed set of the data that exists in the database. The de-normalized information is structured like the following (including made-up data):
Code Block
PartNumber Make Model Year
-------------------------------------------------------
835100 ACURA INTEGRA 2004
835100 ACURA INTEGRA 2003
835100 ACURA INTEGRA 2001
835100 ACURA INTEGRA 2000
835100 FORD FOCUS 2002
There is any number of part numbers, makes, etc in this mix. The query that I currently use is utilizing MAX and MIN functions to determine year range, but as in the sample above, this does not take into account the fact that there could be a year skipped in the middle. I could probably use a cursor to do this, but would really like to stick to set logic if at all possible.
My existing query is:
Code Block
SELECT DISTINCT
cwi.PartNumber
,RTRIM(lv.Make) AS Make
,MIN(lv.Year) AS StartYear
,MAX(lv.Year) AS EndYear
FROM
CWIParts AS cwi
INNER JOIN PartTypes AS pt
ON cwi.PartTypeID = pt.PartTypeID
INNER JOIN PartDetail AS pd
ON cwi.PartNumber = pd.PartNumber
INNER JOIN Status AS s
ON pd.StatusCode = s.StatusCode
INNER JOIN LegacyVehicle AS lv
ON cwi.LegacyVehicleID = lv.LegacyVehicleID
WHERE
cwi.PartTypeID = 10
AND s.Status = 'Active'
GROUP BY
lv.Make
ORDER BY
cwi.PartNumber ASC
,cwi.Make ASC
In hopes to end up with a result set that has 1 row for each unique part-number, make, and consecutive year range relevant to the part-number and make. I will also need to add the functionality to add other attributes (such as Model and Liters), but those are equality based and can be passed in easily.
I had done most of this programatically in vb.net, but I would much rather push this logic back to SQL Server. Thank you all for any help that you can provide on this topic.
- Jay Soares
View 9 Replies
View Related
Oct 17, 2006
Just want to double check this.To add 30 days to the current date in a stored procedure using SQL Server should be this: DATEADD(day, 30, GETDATE())Right?Thanks,Zath
View 1 Replies
View Related
Mar 1, 2006
Hi
Hope someone can help.
I've got to produce a report that filters information dependent on the purchase date based on various parameters.
The parameters are:
@yearend (Datetime)
@months(int)
the script that I wrote in SQL is
select assetno from assets where purch_date < = dateadd(mm,-@months, @yearend)
but when I try to use this in the dataset it comes up with the following error:
"Application usess a value of the wrong type for the current operation"
Any help would be very much appreciated.
View 3 Replies
View Related
Feb 6, 2006
Hi guys I am trying to convert this to date add because MS SqL and Jet SQL dont exactly speak the same language
=DateSerial(Year([FINALSUIT])4,Month([FINALSUIT]),Day([FINALSUIT]))
I am trying to calcuate the finalsuit date for every 2 years becasue that when employees have to get their gaming license renewed, so I'm trying to add the renewal year by 2 because they have to get it done every two years
View 2 Replies
View Related
Mar 22, 2007
If I am running a scheduled job at the first of the month and I want to run an update sequence for the previous month, i was essentially using this sql:
@startdate = dateadd(mm, -1, getdate())
@enddate = dateadd(dd, -1, getdate())
So, will the @enddate represent the last day in the previous month or will that not work?
The Yak Village Idiot
View 1 Replies
View Related
Apr 17, 2008
What is the correct format for getting the last three months of data. in sql its DATEADD(mm-3,getdate()) but for some reason the report doesnt want to take this? what am i doing wrong?
Code Snippet
=IIF(Sum(iif(Fields!Database_Size_Datetime.Value >= DATEADD(mm, - 3, GETDATE()), Cdbl(Fields!MonthlyDBTotal.Value), 0.0))/ 3)
By the way, im trying to get the average over the last three months by doing this.
View 3 Replies
View Related
Sep 26, 2006
I am using Derived Column Transformation Task to format my data as I am converting my DTS to SSIS. One of the columns requires to use DATEADD where I add 1 month and substruct one day from the date that I get in the flat file.(note that I always hardcode the day to "01" regardless of what I get in my flat file)
The current script looks like this:
DTSDestination("PeriodEndDate") =
dateadd("d",-1,dateadd("m",1,cdate(mid(cstr(DTSSource("Col004")),1,4) & "-" & mid(cstr(DTSSource("Col004")),5,2) & "-" & "01")))
Does any one know how to convert it so it works in my derived column?
Thanks...
Maria
View 3 Replies
View Related
Nov 19, 2007
when I execute the following:
select dateadd(dd,92710,1/1/1753)
I get 2153-10-31 00:00:00.000
in managed code I get the right answer 11/1/2006. Between 1753 and 2007, there should have been 2007 - 1753 = 254 yrs x approx 365 = 92,710 days. Where is the year 2153 coming from?
View 1 Replies
View Related
Jan 13, 2000
I've set up a SQL7 database with MSAccess97 as a front end. I'm trying to enter a person with a birthdate prior to 1900, get an ODBC call error, "Datetime field overflow". How to enter dates prior to year 1900?
Thanks.
View 1 Replies
View Related
Jan 6, 2004
thanks for reading.
i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?
the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY
problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.
again, thanks for reading ... and any help in advance.
SELECTcount(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data
WHEREstart_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'
UNION
SELECTcount(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data
WHEREstart_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'
UNION
...
expected output....
Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
.... ....
View 4 Replies
View Related
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
View Related
Oct 13, 2006
Hi folks,A DTS package we have run for years now no longer works. The specificpart that is not working is a subquery in the SOURCE object of atransformation. The source is based on a Microsoft Data Link to aSybase database (DSN changed a couple months ago but the connectionstring was updated successfully for the new 12.51 version of ASE) andthe destination is a link to a local SQL Server 2000 database.The transformation has always worked and when I remove the subqueryeverything works OK. The problem is that I need the subquery!Does anyone have a clue what is going on?Here is the full query.select TableKey = RVSN_TYPE_ID,TableCode = RVSN_TYPE,RevisionDate = RVSN_DATE,RevisionReasonCode = RSN_CODE,RevisionGroup = RVSN_GRP_ID,RevisedField = (select L.FieldIDfrom tempdb.guest.lkpRevisedField Lwhere L.TableID = R.RVSN_TYPEand L.FieldName = R.CHNG_FLD),RevisedValue = OLD_FLD_VAL,RevisionTimestamp = RVSN_TIMESTAMPfrom RVSN R,tempdb.guest.MaxTimeStamp TSwhere R.RVSN_TIMESTAMP TS.Rtimestampand R.RVSN_TIMESTAMP is NOT NULLJohn H.
View 1 Replies
View Related
Aug 29, 2007
Hi All,
I have the following table "Project"
-------------------------------------------------------------------------------------------
ID Name Start Date End Date
--------------------------------------------------------------------------------------------
001 Project 1 2-2-2003 2-3-2007
002 Project 2 1-24-2003 2-6-2007
003 Project 3 4-10-2005 2-10-2008
004 Project 4 5-20-2006 6-6-2008
...
015 Project 15 2-20-2006 3-3-2009
----------------------------------------------------------------------------------------------
What I want is the the following output.
Output 1:
------------------------------------------------------------------------------------------------------------------------------------------------
Year Projects No. Of Projects Starting Months
------------------------------------------------------------------------------------------------------------------------------------------------
2003 Project 1 , Project 2 2 February, January
2005 Project 3 1 April
2006 Project 4, Project 15 2 May , Feb
------------------------------------------------------------------------------------------------------------------------------------------------
(the order displayed in the months shoudl be in accordance with the order of the projecs in the projects column...)
and also the following
Output 2: (this is optional view...)
-----------------------------------------------------------------------------------------
Year Start Date Project Name
-----------------------------------------------------------------------------------------
2003
February 2 Project 1
January 24 Project 2
2005
April 4 Project 3
2006
May 5 Project 4
February 20 Project 15
-------------------------------------------------------------------------------------------
I am very much in need of Output 1. Could someone help me,
View 5 Replies
View Related
Nov 5, 2015
Using 'TAIL([Time].[Time].[Year].MEMBERS,1)' I can get the current year dynamically. My question is how do I get the previous two years dynamically as well. I've tried a few different ways with no luck. As you can see below I'd like to replace 2014 and 2013 with current year -1 and current year -2.
SELECT
{
[Time].[Time].[Year].&[2013],
[Time].[Time].[Year].&[2014],
TAIL([Time].[Time].[Year].MEMBERS,1)
}ON COLUMNS,
NON EMPTY
{[Branches].[Branches].[Region]*[Measures].[Ship Resale S&D Run Rate]} ON ROWS
FROM SALES
View 9 Replies
View Related
