How to get the first and last date of the current

问题:

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

回答1:

SELECT
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear,
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS EndOfYear

The above query gives a datetime value for midnight at the beginning of December 31. This is about 24 hours short of the last moment of the year. If you want to include time that might occur on December 31, then you should compare to the first of the next year, with a < comparison. Or you can compare to the last few milliseconds of the current year, but this still leaves a gap if you are using something other than DATETIME (such as DATETIME2):

SELECT
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear,
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS LastDayOfYear,
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0) AS FirstOfNextYear,
   DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)) AS LastTimeOfYear

Tech Details

This works by figuring out the number of years since 1900 with DATEDIFF(yy, 0, GETDATE()) and then adding that to a date of zero = Jan 1, 1900. This can be changed to work for an arbitrary date by replacing the GETDATE() portion or an arbitrary year by replacing the DATEDIFF(...) function with "Year - 1900."

 SELECT
   DATEADD(yy, DATEDIFF(yy, 0, '20150301'), 0) AS StartOfYearForMarch2015,
   DATEADD(yy, 2015 - 1900, 0) AS StartOfYearFor2015

回答2:

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()))

回答3:

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))  

回答4:

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.

回答5:

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]

回答6:

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

回答7:

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))

回答8:

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

回答9:

select to_date(substr(sysdate,1, 4) || '01/01'), to_date(substr(sysdate,1, 4) || '12/31') 
from dual

回答10:

SELECT DATEADD(DD,-DATEPART(DY,GETDATE())+1,GETDATE())

回答11:

print Cast('1/1/' + cast(datepart(yyyy, getdate()) as nvarchar(4)) as date)

回答12:

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).

回答13:

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.

回答14:

Another way: (Since SQL Server 2012)

SELECT
    DATEFROMPARTS(YEAR(GETDATE()), 1, 1) FirstDay,
    DATEFROMPARTS(YEAR(GETDATE()),12,31) LastDay

回答15:

Try this:

DATE_FORMAT(NOW(),'01/01/%Y')
DATE_FORMAT(NOW(),'31/12/%Y')

回答16:

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

回答17:

---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