entityMap|blocks|key|7hvj0|text|这里有一个相当简单的方法；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1iu69|SELECT+DATEFROMPARTS(YEAR(GETDATE()),+1,+1)+AS+'First+Day+of+Current+Year';
SELECT+DATEFROMPARTS(YEAR(GETDATE()),+12,+31)+AS+'End+of+Current+Year';|code-block|syntax|javascript|79hre|这不性感，但很管用。^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|J|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|K|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|L|9|@]|A|@]|B|$]]]]

Here's a fairly simple way;

<pre><code>SELECT DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AS 'First Day of Current Year';
SELECT DATEFROMPARTS(YEAR(GETDATE()), 12, 31) AS 'End of Current Year';
</code></pre>

It's not sexy, but it works.

entityMap|blocks|key|75qp3|text|您可以使用以下命令获取当前年份DATEPART函数从使用getUTCDate()|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|as9f9|SELECT+
++++'01/01/'+%2B+CONVERT(VARCHAR(4),+DATEPART(yy,+getUTCDate())),+
++++'31/12/'+%2B+CONVERT(VARCHAR(4),+DATEPART(yy,+getUTCDate()))|code-block|syntax|javascript^0|F|8|S|C|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@$A|M|B|N|C|D]|$A|O|B|P|C|D]]|E|@]|F|$]]|$2|G|4|H|6|I|8|Q|9|@]|E|@]|F|$J|K]]]]

You can get the current year using <code>DATEPART</code> function, from the current date obtained using <code>getUTCDate()</code> 

<pre><code>SELECT 
 '01/01/' + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate())), 
 '31/12/' + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))
</code></pre>

entityMap|blocks|key|76dar|text|简单地写：-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5k699|select+convert+(date,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0))|code-block|syntax|js|540jp|一年的开始日期。|e5dt|select+convert+(date,DATEADD(YEAR,+DATEDIFF(YEAR,0,GETDATE())+%2B+1,+-1))^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|M|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|N|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|O|9|@]|A|@]|B|$F|G]]]]

simply write:-

<pre><code>select convert (date,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0))
</code></pre>

start date of the year.

<pre><code>select convert (date,DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()) + 1, -1)) 
</code></pre>

entityMap|blocks|key|fmv5o|text|每年的第一个日期是第一个日期，最后一个日期是31个日期，您只需将年份附加到该日期和月份即可，例如：-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|coi4h|SELECT+'01/01/'%2Bcast(year(getdate())+as+varchar(4))+as+[First+Day],
+'12/31/'%2Bcast(year(getdate())+as+varchar(4))+as+[Last+Day]|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

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

<pre><code> SELECT '01/01/'+cast(year(getdate()) as varchar(4)) as [First Day],
 '12/31/'+cast(year(getdate()) as varchar(4)) as [Last Day]
</code></pre>

entityMap|blocks|key|3tqd|text|要获得一年中的第一天和最后一天，可以使用CONCAT函数。结果值可以转换为任何类型。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|51joi|CONCAT(YEAR(Getdate()),'-01-01')+FirstOfYear,
CONCAT(YEAR(GETDATE()),'-12-31')+LastOfYear|code-block|syntax|javascript^0|K|6|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@$A|M|B|N|C|D]]|E|@]|F|$]]|$2|G|4|H|6|I|8|O|9|@]|E|@]|F|$J|K]]]]

To get the first and the last day of the year, one can use the <code>CONCAT</code> function. The resulting value may be cast to any type.

<pre><code>CONCAT(YEAR(Getdate()),'-01-01') FirstOfYear,
CONCAT(YEAR(GETDATE()),'-12-31') LastOfYear
</code></pre>

entityMap|blocks|key|dk8vc|text|另一种方式：(从SQL+Server+2012开始)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|djdnd|SELECT
++++DATEFROMPARTS(YEAR(GETDATE()),+1,+1)+FirstDay,
++++DATEFROMPARTS(YEAR(GETDATE()),12,31)+LastDay|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

Another way: (Since SQL Server 2012)

<pre><code>SELECT
 DATEFROMPARTS(YEAR(GETDATE()), 1, 1) FirstDay,
 DATEFROMPARTS(YEAR(GETDATE()),12,31) LastDay
</code></pre>

entityMap|blocks|key|dav4l|text|对于本年度的开始日期：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|8sj17|SELECT+DATEADD(DD,-DATEPART(DY,GETDATE())%2B1,GETDATE())|code-block|syntax|js|223t0|对于本年度的结束日期：|ei89s|SELECT+DATEADD(DD,-1,DATEADD(YY,DATEDIFF(YY,0,GETDATE())%2B1,0))^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|M|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|N|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|O|9|@]|A|@]|B|$F|G]]]]

For start date of current year:

<pre><code>SELECT DATEADD(DD,-DATEPART(DY,GETDATE())+1,GETDATE())
</code></pre>

For end date of current year:

<pre><code>SELECT DATEADD(DD,-1,DATEADD(YY,DATEDIFF(YY,0,GETDATE())+1,0))
</code></pre>

entityMap|blocks|key|cmmp1|text|最好的方法是提取当前年份，然后使用连接，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5152a|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|code-block|syntax|javascript|2qa41|这为您提供了：start+:+2020-01-01++and+++end+:+2020-31-12日期格式。|offset|length|style|CODE^0|0|0|7|16^^$0|$]|1|@$2|3|4|5|6|7|8|N|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|O|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|P|9|@$J|Q|K|R|L|M]]|A|@]|B|$]]]]

The best way is to extract the current year then use concatenation like this :
<pre><code>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
</code></pre>
That gives you : <code>start : 2020-01-01 and end : 2020-31-12</code> in date format.

entityMap|blocks|key|ek9c5|text|看看这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|788fb|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|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

Check out this one:

<pre><code>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
</code></pre>

entityMap|blocks|key|4hnvm|text|SELECT+DATEADD(DD,-DATEPART(DY,GETDATE())%2B1,GETDATE())|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|js^0^^$0|$]|1|@$2|3|4|5|6|7|8|E|9|@]|A|@]|B|$C|D]]]]

<pre><code>SELECT DATEADD(DD,-DATEPART(DY,GETDATE())+1,GETDATE())
</code></pre>

entityMap|blocks|key|f6mg9|text|print+Cast('1/1/'+%2B+cast(datepart(yyyy,+getdate())+as+nvarchar(4))+as+date)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|js^0^^$0|$]|1|@$2|3|4|5|6|7|8|E|9|@]|A|@]|B|$C|D]]]]

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

entityMap|0|type|LINK|mutability|MUTABLE|data|url|https://docs.microsoft.com/en-us/sql/t-sql/functions/year-transact-sql|1|https://docs.microsoft.com/en-us/sql/t-sql/functions/day-transact-sql|2|https://docs.microsoft.com/en-us/sql/t-sql/functions/month-transact-sql|blocks|key|eqpj2|text|看起来像是您对执行给定年份的所有操作都很感兴趣，如果确实是这样，我建议您使用YEAR()函数如下：|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|6dcop|SELECT+*+FROM+`table`+WHERE+YEAR(date_column)+=+'2012';|code-block|syntax|js|7a01s|同样的道理也适用于DAY()和MONTH()..。它们也可用于MySQL/MariaDB变体，并在SQL+Server2008中引入(因此不适用于特定的2000年)。^0|0|5|12|6|0|0|0|9|5|1|F|7|2^^$0|$1|$2|3|4|5|6|$7|8]]|9|$2|3|4|5|6|$7|A]]|B|$2|3|4|5|6|$7|C]]]|D|@$E|F|G|H|2|I|J|X|K|@$L|Y|M|Z|N|O]]|P|@$L|10|M|11|E|12]]|6|$]]|$E|Q|G|R|2|S|J|13|K|@]|P|@]|6|$T|U]]|$E|V|G|W|2|I|J|14|K|@]|P|@$L|15|M|16|E|17]|$L|18|M|19|E|1A]]|6|$]]]]

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 <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/year-transact-sql" rel="nofollow noreferrer">YEAR()</a> function like this:

<pre><code>SELECT * FROM `table` WHERE YEAR(date_column) = '2012';
</code></pre>

The same goes for <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/day-transact-sql" rel="nofollow noreferrer">DAY()</a> and <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/month-transact-sql" rel="nofollow noreferrer">MONTH()</a>. They are also available for MySQL/MariaDB variants and was introduced in SQL Server 2008 (so not for specific 2000).

entityMap|blocks|key|1l7vg|text|获取一年的第一个日期和最后一个日期的最好方法是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|gste|SELECT+CAST(CAST(YEAR(DATEADD(YEAR,-1,GETDATE()))+AS+VARCHAR)+%2B+'-'+%2B+'01'+%2B+'-'+%2B+'01'+AS+DATE)+FIRST_DATE|code-block|syntax|js|986mo|SELECT+CAST(CAST(YEAR(DATEADD(YEAR,-1,GETDATE()))+AS+VARCHAR)+%2B+'-'+%2B+'12'+%2B+'-'+%2B+'31'+AS+DATE)+LAST_DATE^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|J|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|K|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|E|8|L|9|@]|A|@]|B|$F|G]]]]

The best way to get First Date and Last Date of a year Is
<pre><code>SELECT CAST(CAST(YEAR(DATEADD(YEAR,-1,GETDATE())) AS VARCHAR) + '-' + '01' + '-' + '01' AS DATE) FIRST_DATE
</code></pre>
<pre><code>SELECT CAST(CAST(YEAR(DATEADD(YEAR,-1,GETDATE())) AS VARCHAR) + '-' + '12' + '-' + '31' AS DATE) LAST_DATE
</code></pre>

entityMap|blocks|key|d23b4|text|select+to_date(substr(sysdate,1,+4)+%7C%7C+'01/01'),+to_date(substr(sysdate,1,+4)+%7C%7C+'12/31')+
from+dual|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript^0^^$0|$]|1|@$2|3|4|5|6|7|8|E|9|@]|A|@]|B|$C|D]]]]

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

entityMap|blocks|key|b9p9k|text|在Microsoft+SQL+Server+(T-SQL)中，可以执行以下操作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1e0f0|--beginning+of+year
select+'01/01/'+%2B+LTRIM(STR(YEAR(CURRENT_TIMESTAMP)))

--end+of+year
select+'12/31/'+%2B+LTRIM(STR(YEAR(CURRENT_TIMESTAMP)))|code-block|syntax|javascript|1ab3q|当前_时间戳-返回执行查询时的sql+server日期。|offset|length|style|BOLD|a6cvr|年份-获取当前时间戳的年份部分。|9asev|应力，LTRIM-应用这两个函数，以便我们可以将其转换为可以与所需前缀连接的varchar+(在本例中，它要么是一年中的第一个日期，要么是一年中的最后一个日期)。无论出于何种原因，由年份函数有前缀空格。为了修复它们，我们使用LTRIM函数是左修剪的。^0|0|0|0|6|0|0|2|0|0|8|2J|2|34|5^^$0|$]|1|@$2|3|4|5|6|7|8|R|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|S|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|T|9|@$J|U|K|V|L|M]]|A|@]|B|$]]|$2|N|4|O|6|7|8|W|9|@$J|X|K|Y|L|M]]|A|@]|B|$]]|$2|P|4|Q|6|7|8|Z|9|@$J|10|K|11|L|M]|$J|12|K|13|L|M]|$J|14|K|15|L|M]]|A|@]|B|$]]]]

In Microsoft SQL Server (T-SQL) this can be done as follows

<pre><code>--beginning of year
select '01/01/' + LTRIM(STR(YEAR(CURRENT_TIMESTAMP)))

--end of year
select '12/31/' + LTRIM(STR(YEAR(CURRENT_TIMESTAMP)))
</code></pre>

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.

entityMap|blocks|key|ak7pb|text|如果到了1月1日，你可能还是去年的日期。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|52k18|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()))%2B1,+-1),+103+)AS+EndOfYear|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

If it reaches the 1st of Jan you might it to be still last years date.

<pre><code>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
</code></pre>

entityMap|blocks|key|6f3ko|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|7btkg|DATE_FORMAT(NOW(),'01/01/%25Y')
DATE_FORMAT(NOW(),'31/12/%25Y')|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

Try this:

<pre><code>DATE_FORMAT(NOW(),'01/01/%Y')
DATE_FORMAT(NOW(),'31/12/%Y')
</code></pre>

entityMap|blocks|key|8hqe|text|-Lalmuni演示|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5hf3n|create+table+Users
(
userid+int,date_of_birth+date
)|code-block|syntax|javascript|553id|-插入值|4l7fg|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^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|M|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|N|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|O|9|@]|A|@]|B|$F|G]]]]

---Lalmuni Demos---

<pre><code>create table Users
(
userid int,date_of_birth date
)
</code></pre>

---insert values---

<pre><code>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)) &gt; 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
</code></pre>

Using SQL Server 2000, how can I get the first and last date of the current year?

Expected Output:

<code>01/01/2012</code> and <code>31/12/2012</code>

How to get the first and last date of the current year?

SQL Server

MariaDB

MySQL

 使用SQL Server2000，如何获取本年度的第一个和最后一个日期？ 预期输出： 01/01/2012和31/12/2012

问如何获取本年度的第一个和最后一个日期？
EN

回答 18

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取本年度的第一个和最后一个日期？EN

回答 18

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取本年度的第一个和最后一个日期？
EN