将日期的月份和年份分成两列 [英] divide or seperate date's month and year in to two column
问题描述
我想把几个月和几年分成两个单独的列。
TranDate
31 / 01/2013
31/02/2013
31/03/2013
i想创建sql查询将从一列中分隔月份和年份，并将月份放入一列，将年份放入其他列。
所以总共会有三列。
例如：
TranDate月份
31/12/2013 12 2012
31/01/2013 01 2013
31/02/2013 02 2013
i want to divide months and years in to two seperate colums.
TranDate
31/01/2013
31/02/2013
31/03/2013
i want to creat sql query which will seperate month and year from one column and put months into one column and year in to other column.
so in total there will be three column.
eg:
TranDate month year
31/12/2013 12 2012
31/01/2013 01 2013
31/02/2013 02 2013
推荐答案
您好试试这个
Hi Try This
SELECT TranDate, MONTH(TranDate) 'TranMonth', YEAR(TranDate) 'TranYear'
FROM table_name
检查所有系统日期功能
日期和时间函数（Transact-SQL） [ ^ ]
问候，
GVPrabu
Check all System Date Functions
Date and Time Functions (Transact-SQL)[^]
Regards,
GVPrabu
SELECT TranDate, MONTH(TranDate) as 'month', YEAR(TranDate) as 'year' FROM TableName
OP返回时出现错误，表明他的TranDate列不是日期时间所以他可以将它转换为日期时间
OP has come back with an error that indicates his TranDate column is not a datetime so he can either convert it to a datetime
SELECT TranDate, MONTH(convert(datetime, TranDate, 103)) as 'month', YEAR(convert(datetime, TranDate, 103)) as 'year' FROM TableName
或使用SUBSTRING获取他想要的零件
or use SUBSTRING to get the parts he wants
SELECT TranDate, SUBSTRING(TranDate, 4,2), SUBSTRING(TranDate, 7,4)
每个都有它们的优点和缺点，取决于你对thr数据库上的数据是否正确的确定
Each have their pros and cons, depending on how sure you are that the data on thr database is correct
1。通过以下声明更改您的数据类型
ALTER TABLE Table_Name ALTER COLUMN TranDate DateTime
2.使用子串（表达式，开始，结束）
SELECT TranDate，子串（TranDate，4,5）AS月，子串（TranDate，7， 10）AS年
来自Table_Name
1. Change your Datatype by following statement
ALTER TABLE Table_Name ALTER COLUMN TranDate DateTime
2. USE SubString(Expression, Start, End)
SELECT TranDate, substring(TranDate,4,5) AS Month, substring(TranDate,7,10) AS Year
FROM Table_Name
这篇关于将日期的月份和年份分成两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
