Need SQL Function to return qualified dates
-
Would someone happen to have some T-SQL (preferrably a FUNCTION) to accomodate this? I need to return all rows from a single table where the Insert_Date = the following criteria:
select * from TAB_X where Insert_Date in
(last day of last year
or
quarter end date for each of last 2 quarters
or
last day of prior month)
Example:
If it's Oct 4th 2006, the query should return all rows w/ Insert_Date =
12/31/2005
06/30/2006
09/30/2006
BT -
Hi,
IMHO if you're doing such reports on regular basis, you should have auxiliary tables (like table Dates containing all dates 20 years back and 30 years forward). In such table, you can then once and for all time mark certain dates as relevant for certain report or simply mark end of month, end of quarter, state holidays, company holidays etc. This will allow you to JOIN to that table in queries, which should improve performance and allow queries that would be impossible without it (like precise calculation of workdays between two dates).
Without such table, you can do this (use this SQL in a procedure or function, or just as it is directly in the report - depends on you). Make sure you strip away the time portion from your Insert_Date... at least I suppose that Insert_Date has time entered. But even if it doesn't at the moment, it would be a good precaution to do so.
DECLARE @date datetime, @lastmonth datetime, @lastquarter datetime,
@prevquarter datetime, @lastyear datetime
SET @date = GETDATE() /*enter your date here*/
/*finds first day of this month and subtrats one day*/
SET @lastmonth = DATEADD(day, -1, CONVERT(char(6), @date, 112) + '01')
/*check whether last month was end of quarter; if not, loop back month by month till you get there*/
SET @lastquarter = @lastmonth
WHILE SUBSTRING(CONVERT(char(6),@lastquarter,112),5,2) NOT IN ('03','06','09','12')
BEGIN
SET @lastquarter = DATEADD(day, -1, CONVERT(char(6), @lastquarter, 112) + '01')
END
/*subtract another quarter - because of different number of days in months, add one day first, subtract 3 months and then add the day back*/
SET @prevquarter = DATEADD(month,-3,@lastquarter + 1) - 1
/*subtract one year*/
SET @lastyear = CONVERT(char(4), DATEADD(year,-1,@date),112)+ '1231'
/*just to check dates, whether it works well*/
select @date ,
@lastmonth as lastmonth,
@lastquarter as lastquarter,
@prevquarter as prevquarter,
@lastyear as lastyear
/*the actual select you need*/
select * from TAB_X where DATEADD(d, DATEDIFF(d, 0, Insert_Date), 0) in (@lastmonth,@lastquarter,@prevquarter,@lastyear)
-
Hello,
You could make 4 seperate functions which take a date as input parameter (e.g. @date) from the code I pasted below. I hope this is what you need .
--input argument
declare @date datetime
--testcases
set @date = '2006-10-04'
--set @date = '2006-08-04'
--set @date = '2006-06-04'
--set @date = '2006-01-01'
--set @date = '2006-02-28'
--set @date = '2006-12-31'
--local variables
declare @LastDayOfLastYear datetime, @PreviousQuarterEnd datetime, @SecondPreviousQuarterEnd datetime, @LastDayOfLastMonth datetime
--improve readability throughout code
set @LastDayOfLastYear = @date
set @PreviousQuarterEnd = @date
set @SecondPreviousQuarterEnd = @date
set @LastDayOfLastMonth = @date
--get last day of previous year
set @LastDayOfLastYear = dateadd( month, -( month(@LastDayOfLastYear) - 1), @LastDayOfLastYear)
set @LastDayOfLastYear = dateadd( day, -( day(@LastDayOfLastYear)), @LastDayOfLastYear)
--get last day of quarter before previous quarter
set @SecondPreviousQuarterEnd = dateadd( day, - day( min( @SecondPreviousQuarterEnd)), dateadd( month, -3, dateadd( month, - (( month( min( @SecondPreviousQuarterEnd)) - 1) % 3), min( @SecondPreviousQuarterEnd))))
--get last day of previous quarter
set @PreviousQuarterEnd = dateadd( day, - day( min( @PreviousQuarterEnd)), dateadd( month, - (( month( min( @PreviousQuarterEnd)) - 1) % 3), min( @PreviousQuarterEnd)))
--get last day of previous year
set @LastDayOfLastMonth = dateadd( day, -( day(@LastDayOfLastMonth)), @LastDayOfLastMonth)
--test results
select @LastDayOfLastYear, @SecondPreviousQuarterEnd, @PreviousQuarterEnd, @LastDayOfLastMonth
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.You can find me on LinkedIn.
I support The Programmer's Bill of Rights.MCITP, MCDBA, MCSD
-
Erwin,
is there any reason why 4 functions would be more useful than one? Or, when we are at that, more useful than no function?
If you're thinking about re-using the separate functions in several places, well... why should anyone bother to create a function like this:
set @LastDayOfLastYear = dateadd( month, -( month(@LastDayOfLastYear) - 1), @LastDayOfLastYear)
set @LastDayOfLastYear = dateadd( day, -( day(@LastDayOfLastYear)), @LastDayOfLastYear)
if you can achieve the same much easier without UDF:
SELECT CONVERT(char(4), DATEADD(year,-1,@date),112)+ '1231'
-
These replies are near perfect for our solution. ! additional question. Using your SQL:
select dateadd(day,-day(min(getdate())),dateadd(month,-3,dateadd(month,-((month(min(getdate()))-1)%3),min(getdate()))))
HOW can I render this result w/ a TIME = 00:00:00.000?
Many many thx in advance!
BT -
hi Vladan,
Well, as you already stated, reusability is the main reason. Other than that I admit looking at you piece of code is easier to read, but I like to use specific datetime functions instead of conversions.
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.You can find me on LinkedIn.
I support The Programmer's Bill of Rights.MCITP, MCDBA, MCSD
-
hi Bill
You could use a conversion (yeah, I know...) to obtain that result, like so:
select getdate(), convert( datetime, floor( convert( decimal(12, 4), getdate())))
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.You can find me on LinkedIn.
I support The Programmer's Bill of Rights.MCITP, MCDBA, MCSD
-
Use CONVERT to format a date field for reporting.
CONVERT
(varchar, GETDATE(),1): 03/22/06
CONVERT(varchar, GETDATE(),2): 06.03.22
CONVERT(varchar, GETDATE(),3): 22/03/06
CONVERT(varchar, GETDATE(),4): 22.03.06
CONVERT(varchar, GETDATE(),5): 22-03-06
CONVERT(varchar, GETDATE(),6): 22 Mar 06
CONVERT(varchar, GETDATE(),7): Mar 22, 06
CONVERT(varchar, GETDATE(),8): 09:53:00
CONVERT(varchar, GETDATE(),9): Mar 22 2006 9:53:00:227AM
CONVERT(varchar, GETDATE(),10): 03-22-06
CONVERT(varchar, GETDATE(),11): 06/03/22
CONVERT(varchar, GETDATE(),12): 060322
CONVERT(varchar, GETDATE(),13): 22 Mar 2006 09:53:00:227
CONVERT(varchar, GETDATE(),14): 09:53:00:227
CONVERT(varchar, GETDATE(),20): 2006-03-22 09:54:58
CONVERT(varchar, GETDATE(),21): 2006-03-22 09:54:58.117
CONVERT(varchar, GETDATE(),22): 03/22/06 9:54:58 AM
CONVERT(varchar, GETDATE(),23): 2006-03-22
CONVERT(varchar, GETDATE(),24): 09:54:58
CONVERT(varchar, GETDATE(),25): 2006-03-22 09:54:58.117
CONVERT(varchar, GETDATE(),101): 03/22/2006
CONVERT(varchar, GETDATE(),102): 2006.03.22
CONVERT(varchar, GETDATE(),103): 22/03/2006
CONVERT(varchar, GETDATE(),104): 22.03.2006
CONVERT(varchar, GETDATE(),105): 22-03-2006
CONVERT(varchar, GETDATE(),106): 22 Mar 2006
CONVERT(varchar, GETDATE(),107): Mar 22, 2006
CONVERT(varchar, GETDATE(),108): 09:56:22
CONVERT(varchar, GETDATE(),109): Mar 22 2006 9:56:22:257AM
CONVERT(varchar, GETDATE(),110): 03-22-2006
CONVERT(varchar, GETDATE(),111): 2006/03/22
CONVERT(varchar, GETDATE(),112): 20060322
CONVERT(varchar, GETDATE(),113): 22 Mar 2006 09:56:22:257
CONVERT(varchar, GETDATE(),114): 09:56:22:257
CONVERT(varchar, GETDATE(),120): 2006-03-22 09:56:22
CONVERT(varchar, GETDATE(),121): 2006-03-22 09:56:22.257
CONVERT(varchar,GETDATE(),126): 2006-03-22T10:03:37.357
CONVERT(varchar,GETDATE(),127): 2006-03-22T10:03:37.357
CONVERT(varchar,GETDATE(),130): 22 ??? 1427 10:03:37:357AM
CONVERT(varchar,GETDATE(),131): 22/02/1427 10:03:37:357AM
-
You can create a date with zero time portion by using DATEADD(d, DATEDIFF(d, 0, @date), 0) - instead of @date, enter your date that you need to modify... or directly the expression you posted.
Results in SQL I posted are always without time and need no more manipulation.
Erwin, I can understand that UDF helps readability, but it is a question whether it does not degrade performance. This should be tested in the specific production database. And even if you write a function, I think it would be better to include one simple command into it than 2 more or less complicated ones that you posted.
-
Vladan,
You can use the functions to fill variables which you then use in the select statement, like you mentioned in your first post.
Complicated or not, I believe that your code runs equally fast as mine.
Btw, I like your piece of code you use the eliminate the time portion from a date
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.You can find me on LinkedIn.
I support The Programmer's Bill of Rights.MCITP, MCDBA, MCSD
-
1 Final SQL adjustment needed.
This Statement returns the last DAY of the prior QTR. Needs to be adjusted to return a TIME = 00:00:00.000
SELECT
dateadd(day,-day(min(getdate())), dateadd(month,-((month(min(getdate()))-1)%3), min(getdate())))
thx
BT -
My alter ego pulled this together:
This Statement returns the last DAY of the prior QTR with TIME = 00:00:00.000
SELECT
CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,DATEADD(DAY,-DAY(MIN(GETDATE())),DATEADD(MONTH,-((MONTH(MIN(GETDATE()))-1)%3),MIN(GETDATE()))))))
BT
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply