Need SQL to render FIRST day of the year
-
Need SQL to render FIRST day of the year - based on select(getdate)
Examples:
If today's date is Mar 14, 2005 -- I need to render Jan 1, 2005 00:00:00.000
If today's date is Nov 29, 2005 -- I need to render Jan 1, 2005 00:00:00.000
If today's date is Jun 23, 2006 -- I need to render Jan 1, 2006 00:00:00.000
Idea's?
BT -
--FIRST DAY of CURRENT MONTH
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
--FIRST Day of the Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
--FIRST Day of the Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
--LAST Day of Prior Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
--LAST Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))
--LAST Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))
--LAST Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))
--FIRST Monday of the Month
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
BT -
Since everything else in the results you want is a constant but for the year, why not just good old concatenation...?!?!
select 'Jan 1, ' + cast(datepart(yyyy, getdate()) as varchar) + ' 00:00:00.000'
**ASCII stupid question, get a stupid ANSI !!!** -
ISO is better:
SELECT CAST(YEAR(GETDATE()) AS varchar(4))+'0101'
Works for any SET DATEFORMAT or language setting on client or server.
Andy
-
How do you get to the First Day of Prior Month?
I'm trying to use an agent to automate a monthly report that will, without having to input a date, render results for the last calendar month. I'm using the following to get results for the previous day, but need the syntax to get previous calendar month.
SELECT COUNT(*) AS [Total Calls]
FROM History
WHERE (ProjectID IN (1013, 1014)) AND (CallDateTime BETWEEN GETDATE() -1 AND GETDATE())
-
Here's a udf I put together that takes a date input and returns a selected date - since our system only uses dates in the mmddyyy or yyyymmdd format, it only addresses those instances but could be easily extended to other date formats.
--INPUTS:
--RunCode1 - what date to return - see below for codes
--RunCode2 - input date format - 1=mmddyyyy, 2=yyyymmdd - NOT USED AT PRESENT
--RunCode3 - output date format - 1=mmddyyyy, 2=yyyymmdd - also used as default format if RunCode3 value out of range
--TO TEST: select dbo.udf_getSelectedDate('20051013',101,2,2)
CREATE FUNCTION dbo.udf_getSelectedDate
(@DateIn varchar(8),
@RunCode1 int,
@RunCode2 int,
@RunCode3 int)
RETURNS
varchar (8)
AS
BEGIN
DECLARE @DateInDT datetime -- date in formatted as datetime
DECLARE @DateOutDT datetime -- date out formatted as datetime
DECLARE @DateOut varchar(8) -- date out formatted as varchar
-- format input
SET @DateInDT = convert(datetime, @DateIn)
-- calc the date to be returned
IF @RunCode1 = 101
--101 - FIRST Day of Month - selected date
SET @DateOutDT = DATEADD(mm,DATEDIFF(mm,0,@DateInDT), 0)
ELSE IF @RunCode1 = 102
--102 - FIRST Day of Next MonthSET - selected date
SET @DateOutDT = DATEADD(mm,DATEDIFF(mm,0,@DateInDT)+1, 0)
ELSE IF @RunCode1 = 103
--103 - FIRST Day of Prior Month - selected date
SET @DateOutDT = DATEADD(mm,DATEDIFF(mm,0,@DateInDT)-1, 0)
ELSE IF @RunCode1 = 104
--104 - LAST Day of Current Month - selected date
SET @DateOutDT = DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,@DateInDT)+1, 0))
ELSE IF @RunCode1 = 105
--105 - LAST Day of Next Month - selected date
SET @DateOutDT = DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,@DateInDT)+2, 0))
ELSE IF @RunCode1 = 106
--106 - LAST Day of Prior Month - selected date
SET @DateOutDT = DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,@DateInDT), 0))
ELSE IF @RunCode1 = 201
--201 - FIRST Day of Current Year - selected date
SET @DateOutDT = DATEADD(yy,DATEDIFF(yy,0,@DateInDT), 0)
ELSE IF @RunCode1 = 202
--202 - FIRST Day of Next Year - selected date
SET @DateOutDT = DATEADD(yy,DATEDIFF(yy,0,@DateInDT)+1, 0)
ELSE IF @RunCode1 = 203
--203 - FIRST Day of Prior Year - selected date
SET @DateOutDT = DATEADD(yy,DATEDIFF(yy,0,@DateInDT)-1, 0)
ELSE IF @RunCode1 = 204
--204 - LAST Day of Current Year - selected date
SET @DateOutDT = DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,@DateInDT)+1, 0))
ELSE IF @RunCode1 = 205
--205 - LAST Day of Next Year - selected date
SET @DateOutDT = DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,@DateInDT)+2, 0))
ELSE IF @RunCode1 = 206
--206 - LAST Day of Prior Year - selected date
SET @DateOutDT = DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,@DateInDT), 0))
ELSE IF @RunCode1 = 301
--301 - FIRST Day of Current Quarter
SET @DateOutDT = DATEADD(qq,DATEDIFF(qq,0,@DateInDT), 0)
ELSE IF @RunCode1 = 302
--302 - FIRST Day of Next Quarter
SET @DateOutDT = DATEADD(qq,DATEDIFF(qq,0,@DateInDT)+1, 0)
ELSE IF @RunCode1 = 303
--303 - FIRST Day of Last Quarter
SET @DateOutDT = DATEADD(qq,DATEDIFF(qq,0,@DateInDT)-1, 0)
ELSE IF @RunCode1 = 304
--304 - LAST Day of Current Quarter
SET @DateOutDT = DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,@DateInDT)+1, 0))
ELSE IF @RunCode1 = 305
--305 - LAST Day of Next Quarter
SET @DateOutDT = DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,@DateInDT)+2, 0))
ELSE IF @RunCode1 = 306
--306 - LAST Day of Last Quarter
SET @DateOutDT = DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,@DateInDT), 0))
ELSE IF @RunCode1 = 401
--401 - FIRST Monday of Current Month
SET @DateOutDT = DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6-datepart(day,@DateInDT),@DateInDT)), 0)
-- format output
IF @RunCode3 = 1
SET @DateOut = SUBSTRING(CONVERT(varchar(8), @DateOutDT, 12),3, 4) + CONVERT(varchar(4),DATEPART(YEAR, @DateOutDT) ) -- set display format to mmddyyyy
ELSE
SET @DateOut = CONVERT(varchar(4),DATEPART(YEAR, @DateOutDT) ) + SUBSTRING(CONVERT(varchar(8), @DateOutDT, 12),3, 4) -- set display format to yyyymmdd
RETURN (@DateOut)
END
-
Harley,
That's an outstanding idea! Thank you. It's nice to see people treating SQL as a computer language instead of just a repository. I do have a couple of suggestions, though...
1. Numeric codes are difficult to remember and the function would require either external "bedside" documentation of you'd need to make it return it's own method of usage if "Help" were entered as each of the parameters. Functions should be easy and intuitive to use. "Intuitive" in this case means "almost English" and something VERY easy to remember.
2. Make a function only do 1 thing and make it do it well. In this case, that 1 thing should be to get the desired date... not do any formatting. Create a different function to do the formatting so it may also be used elsewhere. Do not call a function in a function because that violates the idea of doing one thing well in any given function.
3. Your function is limited to Current, Previous, and Next month. Your head is definitely in the right place but limiting a function like that violates the whole idea of having such a function. It needs to be able to calculate, say, the first day of 20 months from now or 5 years ago. You get the idea.
4. In the code where it's used, it should not only be readable, but it should be VERY obvious what the function is doing without having to lookup how to use the function. In other words, it should be self-documenting.
With all of those things in mind, I offer the following code as a suggestion for your future endeavors in this area... documentation included... since I'm a bit anal about documentation, you'll find that the embedded documentation is actually longer than code.
CREATE FUNCTION dbo.udf_GetSelectedDate
/**************************************************************************************************
Purpose:
This function accepts multiple parameters to identify a "period" and a date contained in that
period. Based on other parameters, it will return the first day of that period, or the last.
Usage:
dbo.udf_GetSelectDate(@Boundary,@PeriodOffSet,@Period,@DateIn)
Parameter descriptions and possible values:
@Boundary: Describes the desired day of period
1. Possible values are 'First of' (not trapped) and 'Last of' (trapped).
2. If 'Last of' is not used, then it doesn't really matter what the value of @Boundary is... the
first day of the desired period will be returned.
3. If 'Last of' is used, then the last day of the desired period will be returned.
@PeriodOffSet: Describes how many @Period to offset from @DateIN
1. Possible string values are:
'This' - Current @Period containing @DateIN
'Current' - Same as 'This'
'Previous' - One @Period less than the @Period that contains @DateIn
'Next' - One @Period more than the @Period that contains @DateIn
2. Possible numeric values are:
0 - Same as 'This' and 'Current' desicribed above
<0 - Number of @Period less than the @Period that contains @DateIn (-1 same as 'Previous')
>0 - Number of @Period more than the @Period that contains @DateIn (+1 same as 'Next')
May be any number that will allow a legal date to be returned.
@Period: The period type to use
1. Recommended possible string values are: (should be self-explanatory)
'Week' or anything that begins with "W"
'Month' or anything that begins with "M"
'Quarter' or anything that begins with "Q"
'Year' or anything that begins with "Y"
2. If any other value is used other than words beginning with the above single letters,
and error is forced (cannot raise error in a function so a non-date string is equated to a
DATETIME to force an error)
@DateIN: The date to include the period calculations of.
1. May be an instinsicly convertable string representation of any valid date.
2. May be a date calculation that returns a date
3. May be any legal date expression
Example usage: (GETDATE used as the datetime in most of these examples but any date will do)
--Return the first day of this (or current) month (both return identical)
SELECT dbo.udf_GetSelectedDate('First day of','This','Month',GETDATE())
SELECT dbo.udf_GetSelectedDate('First of',0,'Month',GETDATE())
SELECT dbo.udf_GetSelectedDate('anything but last of',0,'Month',GETDATE())
--Return the last day of next week (all return identical)
SELECT dbo.udf_GetSelectedDate('Last of','Next','Week',GETDATE())
SELECT dbo.udf_GetSelectedDate('Last of',1,'Week',GETDATE())
SELECT dbo.udf_GetSelectedDate('Last of',1,'Week from now',GETDATE())
--Return the first day of 3 months ago
SELECT dbo.udf_GetSelectedDate('First of',-3,'Months',GETDATE())
You get the idea
Revision History:
12/04/2005 - Jeff Moden --Initial creation
**************************************************************************************************/
--===== Define the function parameters
(
@Boundary VARCHAR(10),
@PeriodOffSet VARCHAR(10),
@Period VARCHAR(10),
@DateIn DATETIME
)
RETURNS DATETIME AS BEGIN
--===== Declare local variables
DECLARE @DateOUT DATETIME
DECLARE @LastOffSet SMALLINT
SET @LastOffSet = 0
--===== Digitize non-numeric period offsets using instrinsic datatype conversions
SELECT @PeriodOffSet =
CASE @PeriodOffSet
WHEN 'This' THEN 0
WHEN 'Current' THEN 0
WHEN 'Previous' THEN -1
WHEN 'Next' THEN +1
ELSE @PeriodOffSet
END
--===== If the "Last of" anything is requested, modify the offsets to accomodate
IF @Boundary = 'Last of'
SELECT @PeriodOffSet = @PeriodOffSet + 1,
@LastOffSet = -1
--===== Calculate the desired date (1st letter of period used for @Period)
SELECT @DateOut =
CASE LEFT(@Period,1)
WHEN 'W' THEN DATEADD(wk,DATEDIFF(wk,0,@DateIN)+@PeriodOffSet,0)+@LastOffSet
WHEN 'M' THEN DATEADD(mm,DATEDIFF(mm,0,@DateIN)+@PeriodOffSet,0)+@LastOffSet
WHEN 'Q' THEN DATEADD(qq,DATEDIFF(qq,0,@DateIN)+@PeriodOffSet,0)+@LastOffSet
WHEN 'Y' THEN DATEADD(yy,DATEDIFF(yy,0,@DateIN)+@PeriodOffSet,0)+@LastOffSet
ELSE 'Error'
END
--===== Return scalar value and exit
RETURN @DateOut
END
Please send beer... I already have enough pretzels.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
Nicely done - thanks for putting in the time to make the UDF more versatile!
Regards,
Harley
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply