如何将字符串转换为日期格式 [英] How to convert a string into date format
问题描述
我在我的查询中有以下列 date_period2
,该列显示日期为 yyyy-mm
格式,但作为字符串。
date_period2
201304
201305
201306
201307
如何将其转换为DATE格式,以便我可以使用它来获取特定月份的工作日。因此,例如, 201304
将作为DATE而不是字符串转换为 04-2013
。
这是我的查询,它是根据 date_period2
字段计算一个表达式,它的工作不正常,因为它是一个字符串格式: p>
SELECT TOP 1000 [date_period2]
,[amount]
,[amount] /(SELECT 20 + COUNT (*)FROM
(SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,[date_period2]),28)AS theDate
UNION
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0, date_period2]),29)
UNION
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,[date_period2]),30))AS d
WHERE DATEPART(DAY,[date_period2])> ; 28 AND DATEDIFF(DAY,0,[date_period2])%7< 5)AS [Weekly Charge]
FROM [database]。[dbo]。[table]
问题是金额
正在被20分,而不是在月
示例:
date_period2金额费用平均数(金额/当月的总工作日)
201304 1750359.95 87517.9975
所以根据上面的结果收费平均值应为 1750359.95 / 22
,而是将其除以20,输出错误。
如何编辑查询或转换日期以确保输出正确完成?
只需将字符串在一个明确的日期格式,如 yyyy-mm-dd
:
SELECT
CONVERT(DATETIME,
SUBSTRING(date_period2,1,4)+' - '
+ SUBSTRING(date_period2,5,2)+'-01')
将 201304
转换为 2013-04- 01
将直接转换为 DateTime
。
编辑<强>
由于您的源列实际上是一个整数
列,一个更干净的方法是: p>
选择CONVERT(DATETIME,CONVERT(CHAR(6),date_period2)+'01')
$ c $将转换 201304
到20130401
whihc对datetime解析器仍然是明确的。
I have the following column, date_period2
in my query which displays date in yyyy-mm
format but as a string.
date_period2
201304
201305
201306
201307
How can i convert that to a DATE format so I can use it to get the working days of the specific month. So for example, 201304
would be converted to 04-2013
as a DATE instead of a string.
This is my query which is calculating an expression based on the date_period2
field and it's not working because it's a string format:
SELECT TOP 1000 [date_period2]
,[amount]
,[amount]/(SELECT 20 + COUNT(*) FROM
(SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, [date_period2]), 28) AS theDate
UNION
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, [date_period2]), 29)
UNION
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, [date_period2]), 30) ) AS d
WHERE DATEPART(DAY, [date_period2]) > 28 AND DATEDIFF(DAY, 0, [date_period2]) % 7 < 5) AS [Weekly Charge]
FROM [database].[dbo].[table]
The issue is the amount
is being divided by 20 and not by the working days for the month-year.
Example:
date_period2 amount charge average (amount/total working days of the month)
201304 1750359.95 87517.9975
So according to the result above the charge average is supposed to be 1750359.95/22
, but instead it is being divided by 20 giving it the wrong output.
How can I either edit the query or convert the date to ensure the output is done correctly?
解决方案 Just put the string in an unambiguous date format like yyyy-mm-dd
:
SELECT
CONVERT(DATETIME,
SUBSTRING(date_period2,1,4) + '-'
+ SUBSTRING(date_period2,5,2) + '-01')
That will convert 201304
into 2013-04-01
which will convert directly to a DateTime
.
EDIT
Since your source column is actually an integer
column, a cleaner method is:
select CONVERT(DATETIME,CONVERT(CHAR(6),date_period2)+'01')
that will convert 201304
to "20130401"
whihc is still unambiguous to the datetime parser.
这篇关于如何将字符串转换为日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!