如何从DDMMYY格式获取月份值？ [英] How to get month value from DDMMYY format?
问题描述
我有一个名为DATE的列，其中的值就像260713，其中26是DD，07是MM，13是YY。
我想要比较上述日期的特定月份的值。所以我需要像月份和月份这样的东西年价值如
如果日期是2013年7月26日......
I have a column named DATE and values coming into it were like "260713" where 26 is DD, 07 is MM and 13 is YY.
I wanna compare values of particular month from the above date. so i need something like getting month & year values like
If date is 26/07/2013...
MONTH(CONVERT(DATETIME,'26/07/2013')) WHICH GIVES O/P AS '07'
YEAR(CONVERT(DATETIME,'26/07/2013')) WHICH GIVES O/P AS '2013'
如上所示取我月和月的价值从'260713'开始的年份
谢谢..
like as above fetch me values of month & year from date '260713'
Thanks..
推荐答案
这很难看，但是怎么样 SELECT CONVERT（日期，STUFF（STUFF（'260713'，5,0，'/'），3,0，'/'），3）
It's ugly, but how about SELECT CONVERT (date,STUFF(STUFF('260713',5,0,'/'),3,0,'/'),3)
您好，您可以使用Datepart提取日，月，年，虽然您必须先格式化您的文字。
所以假设你有文字'260713'，您想将其更改为YYYYMMDD格式。可能有更好的方法来解析它，但这就是我所做的。
Hi, you can use Datepart to extract the day, month, year, although you have to first format your literal first.
So assuming you have a literal of '260713', you want to change it to YYYYMMDD format. There might be a better way to parse it, but this is what I did.
declare @var varchar(max)
set @var= '260713'
set @var= '20'+Substring(@var,5,2) + Substring(@var, 3,2) + Substring(@var,1,2)
我在前面加了20，所以它变成了四个字母的年份 - > 2013
现在运行以下内容：
I added the 20 in front so it becomes a four letter year --> 2013
Now running the following:
select @var
select Datepart(month, @var)
select Datepart(year, @var)
select Datepart(day, @var)
这将给你：
which will give you:
20130726
7
2013
26
希望这符合您的要求！
Hope this meets your requirements!
首先，不要将日期值存储为文本字符串：始终将它们转换为DateTime vlaues存储它们 - 最好是在它们之前到达SQL附近。这有两个明显的优点：
1）日期输入的文化敏感部分可以尽可能靠近源处理：因此有更好的机会访问文化输入计算机，从而使日期正确。例如，010203是什么日期？ 2001年2月3日？ 2003年1月2日？ 2003年2月1日？所有这些都是有效的，你会发现PC设置使用它们在一些公司中彼此相邻......一旦数据进入SQL，文化信息就会丢失，并且必须猜测。
2）当您想要进行比较时，在数据库中使用DateTime列更容易使用：您具有DATEPART功能，可根据需要提取月中的某一天，一年中的某些月份，以及它已经是一个数值，这意味着比较按预期工作。如果你使用字符串，那么当你比较看似合法的值时，你会得到一些非常奇怪的结果。
First off, don't store date values as text strings: always convert them to DateTime vlaues are store those instead - preferably before they get anywhere near SQL. This has two distinct advantages:
1) The culture sensitive part of the date input can be handled as close to the source as possible: so there is a much better chance of having access to the culture of the input computer, and thus getting the date right. For example, what date is 010203? 3rd Feb 2001? 2nd Jan 2003? 1st Feb 2003? All of those are valid, and you will find PC's set to use them sitting next to each other in some companies... Once the data gets to SQL, the culture info is lost, and it has to "guess".
2) Using DateTime columns in databases is much easier to work with when you want to do comparisons: you have the DATEPART function which extracts the various day of month, month of year etc., as you require, and it is already a numeric value which means that comparisons work as expected. If you use strings, then you can get some very odd results when you compare legitimate looking values.
这篇关于如何从DDMMYY格式获取月份值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
