如何可靠地转换YYYYMMDDHhmmss< offset>到日期/时间? [英] How can I reliably convert YYYYMMDDhhmmss<offset> to a date/time?
问题描述
我有一个数据来源,日期如下所示: 20150614140520-0500
我如何显示更好?实际上并不需要在技术上进行转换,这只是为了报告。理想情况下,它会输出 2015-06-14 2:05 PM(EST)
,但我也希望语句尽可能简单,所以时区偏移和12小时时间是可选的
我试过 CAST('20150614140520-0500'As Date)
,但它返回我也尝试过
CAST(LEFT('20150614140520-0500',14)As Date)
还有一些其他的变体,没有运气。
你必须拿起前14个字符,以上:
print convert(datetime,stuff(stuff(stuff(stuff(LEFT('20150614140520-0500',14) 0,''),12,0,':'),15,0,':'))
I have a data source where the dates come back like this: 20150614140520-0500
How can I display this better? It doesn't actually have to be converted technically, this is just for reporting. Ideally it would output 2015-06-14 2:05 PM (EST)
but I also want the statement to be as simple as possible so the timezone offset and 12hr time are optional.
I tried CAST ('20150614140520-0500' As Date)
but it returns null
I also tried CAST (LEFT('20150614140520-0500',14) As Date)
as well as some other variants without luck.
You have to take the first 14 characters, from the example above:
print convert(datetime,stuff(stuff(stuff(LEFT('20150614140520-0500',14) , 9, 0, ' '), 12, 0, ':'), 15, 0, ':'))
这篇关于如何可靠地转换YYYYMMDDHhmmss< offset>到日期/时间?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!