如何从表列中获取月份 [英] How to get month from a table column
本文介绍了如何从表列中获取月份的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张表,其中一列有这样的月份。
jan-14
feb-14
mar-14
aug-14
apr-14
dec-14
nov-14
我想选择最长月份(例如12月14日)? div class =h2_lin>解决方案
我不会详细介绍您的表模式,因为您知道数据的最佳设计。但是我不会把日期作为字符串存储在第一位。
无论如何,你可以做一些字符串操作并进行类似这样的查询:
SELECT
SUBSTRING(月份, 0 , 4 ) AS 月,
CONVERT ( INT ,SUBSTRING(月份, 5 , 9 )) AS 年,
月(SUBSTRING(月份, 0 , 4 )+ ' 12 2014') AS MonthNumber
FROM
CP
ORDER BY
年DESC ,MonthNumber DESC
在此之后你可以使用MAX,TOP等你想要的东西!
这个怎么样:
DECLARE @ mon VARCHAR ( 32 )= ' sep-14 '
PRINT DATEPART(m, CONVERT ( date ,' 1 - ' + @mon , 6 ))
并且请不要将日期存储为字符串。
您好,
SELECT TOP 1 CONVERT ( DATETIME ,' 01 - ' + ColumnName, 106 ) AS ColumnDate
ORDER BY ColumnDate DESC
谢谢,
Baliram Suryawanshi
I have a table in which a column has month like this.
jan-14
feb-14
mar-14
aug-14
apr-14
dec-14
nov-14
I want to select max month (for ex. dec-14) how ?
解决方案
I am not going into detail of your table schema because you know the best design for your data. But I would not store the date as string in first place.
Anyway, you could do some string manipulation and have a query something like this:
SELECT SUBSTRING (Months, 0 , 4) AS Month, CONVERT(INT, SUBSTRING (Months, 5 , 9)) AS Year, Month(SUBSTRING (Months, 0 , 4) + ' 12 2014') AS MonthNumber FROM CP ORDER BY Year DESC, MonthNumber DESC
After this you can use MAX, TOP etc. to what you want!
How about this:
DECLARE @mon VARCHAR(32) = 'sep-14' PRINT DATEPART(m , CONVERT(date, '1-' + @mon , 6))
And please don't store dates as strings.
Hi,
SELECT TOP 1 CONVERT(DATETIME, '01-'+ ColumnName , 106) AS ColumnDate ORDER BY ColumnDate DESC
Thanks,
Baliram Suryawanshi
这篇关于如何从表列中获取月份的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文