July 2, 2012 at 6:25 pm
hello fellas,i need some code or logic that will always provide the last day of any year.i know its a vague question but any help is appreaciated. thanks
July 2, 2012 at 6:48 pm
Here is an example for the current year:
select LastDayOfYear = dateadd(yy,datediff(yy,-1,getdate()),-1)
Results:
LastDayOfYear
-----------------------
2012-12-31 00:00:00.000
(1 row(s) affected)
For a different year, just replace getdate() with a date in that year.
July 2, 2012 at 8:28 pm
The question I have, is this, the last date of what year? Using today, 2012-07-02, as the date of reference, what year do you want the end of; this year, next year, or last year?
The answer above works for this year.
July 4, 2012 at 8:31 am
The question I have is how are you going to use this? If you are using this in a where clause it is most likely a better option to get the first of the next year (current year) and use less than.
Example:
WHERE datecolumn >= {first of last year}
AND datecolumn < {first of this year}
This will make sure you include all times on the last day of the year - regardless of the precision of the date/time column being used.
Using this:
WHERE datecolumn BETWEEN {first of last year} AND {end of last year}
could exclude everything on the last day of the year where the time is greater than 00:00:00.000.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 4, 2012 at 8:47 am
hello fellas,i need some code or logic that will always provide the last day of any year.i know its a vague question but any help is appreaciated. thanks
Last day will always be 12/31 so why not concantenate '12/31/' with year and then convert to a date or datetime.
I do not see much point of its usage but you can try below to find the last day of year..
drop function LastDayOfAnyYear
go
create function LastDayOfAnyYear
(
@year int
)
returns table
as
return
select CONVERT(date,'12/31/' + convert(varchar(4),@year),101) LastDayOfYear
go
declare @year int = 2012
select * from LastDayOfAnyYear(@year)
--or you could use it with the tnumbers table as welll
select * from nums n
cross apply LastDayOfAnyYear(n.cnt)
where n.cnt between 1900 and 9999
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 4, 2012 at 12:05 pm
To convert an integer year number to the last day of the year, you can use DATEADD without the need to convert to a character string:
-- Convert year number to last day of year
select
a.*,
LastDay = dateadd(yy,a.YearNum-1899,-1)
from
( -- Test Data
select YearNum = 1753 union all
select YearNum = 1899 union all
select YearNum = 1900 union all
select YearNum = 2000 union all
select YearNum = 2001 union all
select YearNum = 2004 union all
select YearNum = 2011 union all
select YearNum = 2012 union all
select YearNum = 9999
) a
order by
a.YearNum
Results:
YearNum LastDay
----------- -----------------------
1753 1753-12-31 00:00:00.000
1899 1899-12-31 00:00:00.000
1900 1900-12-31 00:00:00.000
2000 2000-12-31 00:00:00.000
2001 2001-12-31 00:00:00.000
2004 2004-12-31 00:00:00.000
2011 2011-12-31 00:00:00.000
2012 2012-12-31 00:00:00.000
9999 9999-12-31 00:00:00.000
(9 row(s) affected)
July 5, 2012 at 4:48 am
Starting at the beginning, do you mean Last Day of:
1) The Fiscal Year (the business's accounting year)
2) The Calendar Year
3) The Business Year (slightly different from Fiscal)
4) The PayDay Year cycle
5) The actual Day of the last day of the year (Monday, Tuesday, Wednesday, etc.)
???
It makes a difference in how we answer the question.
July 5, 2012 at 5:14 am
hello fellas,i need some code or logic that will always provide the last day of any year.i know its a vague question but any help is appreaciated. thanks
I think as its the universal truth that 31st dec of any year is the last day of calendar year, then instead of searching it by logic, you should hard code it.
Also if you looking for the day of that particular date then you can use day().
For ex, day(getdate()) will return 5 which means Thursday. This counting starts from 0 as Sunday and then onwards.
----------
Ashish
July 6, 2012 at 8:58 am
crazy4sql (7/5/2012)Also if you looking for the day of that particular date then you can use day().
For ex, day(getdate()) will return 5 which means Thursday. This counting starts from 0 as Sunday and then onwards.
Actually, day returns the day of the month, not the weekday--day(getdate()) today will return 6, for instance, but that's because it's the 6th July, not because it's Friday. You would have to use DATEPART(dw, getdate()) to return the day of the week, that's a 1-based value, not zero-based, and what weekday is number 1 depends on what SET DATEFIRST is configured to--yes, that would be Sunday on a default install of a US English version of SQL Server, but isn't guaranteed to be that way for every installation.
July 6, 2012 at 9:47 am
Gullimeel (7/4/2012)
hello fellas,i need some code or logic that will always provide the last day of any year.i know its a vague question but any help is appreaciated. thanks
Last day will always be 12/31 so why not concantenate '12/31/' with year and then convert to a date or datetime.
I do not see much point of its usage but you can try below to find the last day of year..
drop function LastDayOfAnyYear
go
create function LastDayOfAnyYear
(
@year int
)
returns table
as
return
select CONVERT(date,'12/31/' + convert(varchar(4),@year),101) LastDayOfYear
go
declare @year int = 2012
select * from LastDayOfAnyYear(@year)
--or you could use it with the tnumbers table as welll
select * from nums n
cross apply LastDayOfAnyYear(n.cnt)
where n.cnt between 1900 and 9999
You asked why not hard code and use CONVERT(date,'12/31/' + convert(varchar(4),@year),101) LastDayOfYear. How about because it is slower.
I ran the following code several times and this, CAST(CAST(MyYear AS CHAR(4)) + '-12-31' AS DATE), was about twice as slow this, CAST(DATEADD(yy, MyYear - 1899, -1) AS DATE) when run over 1,000,000 rows of test data.
SET NOCOUNT ON;
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT 2000 + FLOOR(10 * rand(CHECKSUM(NEWID()))) AS MyYear FROM tally)
SELECT
@MyDate = CAST(CAST(MyYear AS CHAR(4)) + '-12-31' AS DATE)
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT 2000 + FLOOR(10 * rand(CHECKSUM(NEWID()))) AS MyYear FROM tally)
SELECT
@MyDate = CAST(DATEADD(yy, MyYear - 1899, -1) AS DATE)
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
SET NOCOUNT OFF;
July 6, 2012 at 10:31 am
How about create procedure sp_lastdayofyear (@year int) as
select Convert(date,convert(varchar(4),@year)+'-12-31')
July 6, 2012 at 10:42 am
dan-572483 (7/6/2012)
How aboutcreate procedure sp_lastdayofyear (@year int) asselect Convert(date,convert(varchar(4),@year)+'-12-31')
No real difference between CAST or CONVERT.
SET NOCOUNT ON;
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT 2000 + FLOOR(10 * rand(CHECKSUM(NEWID()))) AS MyYear FROM tally)
SELECT
@MyDate = CAST(CAST(MyYear AS CHAR(4)) + '-12-31' AS DATE)
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT 2000 + FLOOR(10 * rand(CHECKSUM(NEWID()))) AS MyYear FROM tally)
SELECT
@MyDate = Convert(date,convert(varchar(4),MyYear)+'-12-31')
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
DECLARE @MyDate DATE;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),
sampledata AS (SELECT 2000 + FLOOR(10 * rand(CHECKSUM(NEWID()))) AS MyYear FROM tally)
SELECT
@MyDate = CAST(DATEADD(yy, MyYear - 1899, -1) AS DATE)
FROM
sampledata;
SET STATISTICS TIME OFF;
GO
SET NOCOUNT OFF;
July 6, 2012 at 10:49 am
I've never run into an instance where using CAST over CONVERT (or vice versa) has made a difference in code performance. The problem is usually related to the rest of the code, not the choice of conversion function.
July 6, 2012 at 11:54 am
I need one that will always display the last day of the fiscal year.
July 6, 2012 at 11:57 am
tim.cloud (7/6/2012)
I need one that will always display the last day of the fiscal year.
What is your fiscal year? And do you keep track of it in other ways in your database or do you just happen to know the start month and end month?
Fiscal year is not consistent across businesses (or even governments). The U.S. Government tends to end their fiscal year in October, for instance, while several S&P 500 companies end theirs in March or June, or even August.
Define your fiscal year for us and we can give you that code.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply