SQL Server中的AM PM时间转换来自字符串 [英] AM PM time converstion in SQL Server from string
本文介绍了SQL Server中的AM PM时间转换来自字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
HI全部,
谷歌搜索后,我刚刚登陆CP寻找解决问题的指导。
我们一直在表格中保存时间,如上午11点33分,表格为1133.现在我想以适当的时间格式恢复,即AM PM格式,以便在我们的应用程序中使用。 />
可以任何身体指南吗?
:)
HI All,
After googling alot, I have just landed on CP in search of some guidance to resolve my issue.
We have been saving time in our table like 11:33 AM in table as 1133.Now i want to get it back in proper time format i.e. in AM PM format to use in our application.
Can any body guide??
:)
推荐答案
请先阅读我的评论。
一般来说,要将数据从一种类型转换为另一种类型,请使用:CAST AND CONVERT(Transact-SQL) [ ^ ]
回复OP评论。不,这还不够信息。 MS SQL Server的版本很少。其中一些不提供时间数据类型。
Please read my comment first.
Generally, to convert data from one type to another, please use: CAST and CONVERT (Transact-SQL)[^]
Reply to OP comment. No, it's not enough information. There are few version of MS SQL Server. Some of them does not provide time data type.
DECLARE @t VARCHAR(30) = '1133'
SELECT LEFT(@t,2) + ':' + RIGHT(@t,2) + ' AM' AS MyTimeAsString
SELECT CONVERT(TIME, MyTimeAsString) AS MyTimeAsTime
FROM (
SELECT LEFT(@t,2) + ':' + RIGHT(@t,2) + ' AM' AS MyTimeAsString
) AS T
SELECT CONVERT(DATETIME, MyTimeAsString) AS MyTimeAsDateTime
FROM (
SELECT LEFT(@t,2) + ':' + RIGHT(@t,2) + ' AM' AS MyTimeAsString
) AS T
结果:
Results:
MyTimeAsString
11:33 AM
MyTimeAsTime
11:33:00.0000000
MyTimeAsDateTime
1900-01-01 11:33:00.000
这就是你想要的吗?
[/编辑]
Is that what you want?
[/EDIT]
CREATE FUNCTION udf_FormatTime (@sTime VARCHAR(10))
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @sTimeFormat VARCHAR(15)
IF LEN(@sTime) = 0
SET @sTimeFormat = '00:00 AM'
ELSE
SET @sTimeFormat = LEFT(@sTime, 2) + ':' + RIGHT(@sTime, 2)
IF CONVERT(INT, LEFT(@sTimeFormat,2))<13
SET @sTimeFormat = @sTimeFormat + ' AM'
ELSE
SET @sTimeFormat = @sTimeFormat + ' PM'
RETURN @sTimeFormat
END
[/编辑2]
[/EDIT 2]
这篇关于SQL Server中的AM PM时间转换来自字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文