blocks|key|4397136|text|例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4397137|SELECT+concat(EXTRACT(YEAR+FROM+'2015/1/1'),+'_',+
+++++++++LPAD(extract(month+from+'2014/1/1'),2,'0'))+AS+OrderYear|code-block|syntax|javascript|4397138|这是用|4397139|串接组合字符串|unordered-list-item|4397140|如果月份为1位数，lpad将放置一个前导0。|4397141|并使用提取的部分来挑选所需的日期。|4397142|4397143|4397144|工作小提琴http://sqlfiddle.com/#!2/63b24/8|offset|length|4397145|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!2/63b24/8^0|0|0|0|0|0|0|0|0|5|W|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|15|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|16|8|@]|9|@]|A|$]]|$1|L|3|M|5|K|7|17|8|@]|9|@]|A|$]]|$1|N|3|O|5|K|7|18|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1B|8|@]|9|@$T|1C|U|1D|1|1E]]|A|$]]|$1|V|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|W|$X|$5|Y|Z|10|A|$11|12]]]]

For example: 

<pre><code>SELECT concat(EXTRACT(YEAR FROM '2015/1/1'), '_', 
 LPAD(extract(month from '2014/1/1'),2,'0')) AS OrderYear
</code></pre>

This uses 

<ul>
<li>concat to combine strings</li>
<li>lpad to place a leading 0 if month is one digit</li>
<li>and uses extract to pick of the part of date needed.</li>
</ul>

working fiddle
<a href="http://sqlfiddle.com/#!2/63b24/8" rel="nofollow">http://sqlfiddle.com/#!2/63b24/8</a>

blocks|key|404510|text|select+DATENAME+(YYYY,+EntryDate)+
++++++%2B+'_'+
++++++%2B+right('0'+%2B+convert(varchar(2),datepart+(MM,+EntryDate)),+2)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|404511|您必须将DATEPART()的结果转换为字符串，这样%2B才能执行追加。|unstyled|offset|length|style|CODE|404512|未来“不成功”并不意味着什么。下一次将收到的实际错误发布。|404513|entityMap^0|0|4|A|Q|1|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@$G|Q|H|R|I|J]|$G|S|H|T|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$]]|$1|M|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>select DATENAME (YYYY, EntryDate) 
 + '_' 
 + right('0' + convert(varchar(2),datepart (MM, EntryDate)), 2)
</code></pre>

You have to convert the result of <code>DATEPART()</code> to a character string in order for the <code>+</code> to perform an append.

FYI - in the future "unsuccessful" doesn't mean anything. Next time post the actual error you are receiving.

blocks|key|2735524|text|date_format()怎么样？|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2735525|select+date_format(EntryDate,+'%25&Y_%25m')|code-block|syntax|javascript|2735526|这是MySQL的方式。您的代码看起来像是试图在Server中执行此操作。|2735527|编辑：|2735528|以下内容应适用于Server：|2735529|select+DATENAME(year,+EntryDate)+%2B+'_'+%2B+RIGHT('00'+%2B+DATEPART(month,+EntryDate),+2)|2735530|就我个人而言，我可能使用convert()|2735531|select+replace(convert(varchar(7),+EntryDate,+121),+'-',+'_')|2735532|entityMap^0|0|D|0|0|0|0|0|0|C|9|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|13|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|14|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|U|3|V|5|H|7|19|8|@]|D|@]|E|$I|J]]|$1|W|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|X|$]]

How about <code>date_format()</code>?

<pre><code>select date_format(EntryDate, '%&amp;Y_%m')
</code></pre>

This is the MySQL way. Your code looks like an attempt to do this in SQL Server.

EDIT:

The following should work in SQL Server:

<pre><code>select DATENAME(year, EntryDate) + '_' + RIGHT('00' + DATEPART(month, EntryDate), 2)
</code></pre>

Personally, I might use <code>convert()</code>:

<pre><code>select replace(convert(varchar(7), EntryDate, 121), '-', '_')
</code></pre>

blocks|key|4397239|text|DATEPART它是一个从日期中提取信息的日期时间函数。此函数总是以整数类型返回结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4397240|SELECT+DATEPART(month,+'2009-01-01+00:00:00:000')+as+month
it+is+return+"1"+as+an+integer:|code-block|syntax|javascript|4397241|DATENAME也是从日期中提取信息的另一个Datetime函数。此函数总是以varchar的形式返回结果。|4397242|SELECT+DATENAME(month,+'2009-01-01+00:00:00:000')++as+month
it+is+return+"January".+|4397243|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

DATEPART
It is a Datetime function which extract information from date. This function always returns result as integer type.

<pre><code>SELECT DATEPART(month, '2009-01-01 00:00:00:000') as month
it is return "1" as an integer:
</code></pre>

DATENAME
It is also another Datetime function which to extract information from date. This function always returns result as varchar

<pre><code>SELECT DATENAME(month, '2009-01-01 00:00:00:000') as month
it is return "January". 
</code></pre>

I'm trying to get my EntryDate column in this format 'YYYY_m' for example '2013_04'.

This code has been unsuccessful

<pre><code>DATENAME (YYYY, EntryDate) + '_' + DATEPART (M, EntryDate) 
</code></pre>

Attempts using DATEFORMAT have also been unsuccessful, stating there was syntax error at ',' after the M. What code would work instead?

Thank you.

DATENAME and DATEPART in SQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在尝试将我的EntryDate列设置为'YYYY_m‘格式，例如'2013_04’。此代码未成功。DATENAME (YYYY, EntryDate) + '_' + DATEPART (M, EntryDate) 使用DATEFORMAT的尝试也失败了，声称在M之后出现了语法错误，什么代码可以代替？谢谢。

问SQL中的DATENAME和DATEPART
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL中的DATENAME和DATEPARTEN