Using SQL Server 2000, how can I get the first and last date of the current year?
Expected Output:
01/01/2012
and 31/12/2012
This question is related to
sql
sql-server
sql-server-2000
---Lalmuni Demos---
create table Users
(
userid int,date_of_birth date
)
---insert values---
insert into Users values(4,'9/10/1991')
select DATEDIFF(year,date_of_birth, getdate()) - (CASE WHEN (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()),date_of_birth)) > getdate() THEN 1 ELSE 0 END) as Years,
MONTH(getdate() - (DATEADD(year, DATEDIFF(year, date_of_birth, getdate()), date_of_birth))) - 1 as Months,
DAY(getdate() - (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()), date_of_birth))) - 1 as Days,
from users
Every year has the 1 st as First date and 31 as the last date what you have to do is only attach the year to that day and month for example:-
SELECT '01/01/'+cast(year(getdate()) as varchar(4)) as [First Day],
'12/31/'+cast(year(getdate()) as varchar(4)) as [Last Day]
To get the first and the last day of the year, one can use the CONCAT
function. The resulting value may be cast to any type.
CONCAT(YEAR(Getdate()),'-01-01') FirstOfYear,
CONCAT(YEAR(GETDATE()),'-12-31') LastOfYear
You can get the current year using DATEPART
function, from the current date obtained using getUTCDate()
SELECT
'01/01/' + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate())),
'31/12/' + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))
select to_date(substr(sysdate,1, 4) || '01/01'), to_date(substr(sysdate,1, 4) || '12/31')
from dual
For start date of current year:
SELECT DATEADD(DD,-DATEPART(DY,GETDATE())+1,GETDATE())
For end date of current year:
SELECT DATEADD(DD,-1,DATEADD(YY,DATEDIFF(YY,0,GETDATE())+1,0))
Check out this one:
select convert(varchar(12),(DateAdd(month,(Month(getdate())-1) * -1, DateAdd(Day,(Day(getdate())-1) * -1,getdate()))),103) as StartYear,
convert(varchar(12),DateAdd(month,12 - Month(getdate()), DateAdd(Day,(31 - Day(getdate())),getdate())),103) as EndYear
It looks like you are interesting in performing an operation everything for a given year, if this is indeed the case, I would recommend to use the YEAR() function like this:
SELECT * FROM `table` WHERE YEAR(date_column) = '2012';
The same goes for DAY() and MONTH(). They are also available for MySQL/MariaDB variants and was introduced in SQL Server 2008 (so not for specific 2000).
In Microsoft SQL Server (T-SQL) this can be done as follows
--beginning of year
select '01/01/' + LTRIM(STR(YEAR(CURRENT_TIMESTAMP)))
--end of year
select '12/31/' + LTRIM(STR(YEAR(CURRENT_TIMESTAMP)))
CURRENT_TIMESTAMP - returns the sql server date at the time of execution of the query.
YEAR - gets the year part of the current time stamp.
STR , LTRIM - these two functions are applied so that we can convert this into a varchar that can be concatinated with our desired prefix (in this case it's either first date of the year or the last date of the year). For whatever reason the result generated by the YEAR function has prefixing spaces. To fix them we use the LTRIM function which is left trim.
Here's a fairly simple way;
SELECT DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AS 'First Day of Current Year';
SELECT DATEFROMPARTS(YEAR(GETDATE()), 12, 31) AS 'End of Current Year';
It's not sexy, but it works.
Another way: (Since SQL Server 2012)
SELECT
DATEFROMPARTS(YEAR(GETDATE()), 1, 1) FirstDay,
DATEFROMPARTS(YEAR(GETDATE()),12,31) LastDay
print Cast('1/1/' + cast(datepart(yyyy, getdate()) as nvarchar(4)) as date)
The best way to get First Date and Last Date of a year Is
SELECT CAST(CAST(YEAR(DATEADD(YEAR,-1,GETDATE())) AS VARCHAR) + '-' + '01' + '-' + '01' AS DATE) FIRST_DATE
SELECT CAST(CAST(YEAR(DATEADD(YEAR,-1,GETDATE())) AS VARCHAR) + '-' + '12' + '-' + '31' AS DATE) LAST_DATE
Try this:
DATE_FORMAT(NOW(),'01/01/%Y')
DATE_FORMAT(NOW(),'31/12/%Y')
The best way is to extract the current year then use concatenation like this :
SELECT CONCAT(year(now()), '-01-01') as start, -- fist day of current year
CONCAT(year(now()), '-31-12') as end; -- last day of current year
That gives you : start : 2020-01-01 and end : 2020-31-12
in date format.
SELECT DATEADD(DD,-DATEPART(DY,GETDATE())+1,GETDATE())
simply write:-
select convert (date,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0))
start date of the year.
select convert (date,DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()) + 1, -1))
If it reaches the 1st of Jan you might it to be still last years date.
select
convert(date, DATEADD(yy, DATEDIFF(yy, 0, DATEadd(day, -1,getdate())), 0), 103 ) AS StartOfYear,
convert(date, DATEADD(yy, DATEDIFF(yy, 0, DATEDIFF(day, -1,getdate()))+1, -1), 103 )AS EndOfYear
Source: Stackoverflow.com