SQL Server中的自定义日期/时间格式 [英] Custom Date/Time formatting in SQL Server
问题描述
日期时间格式字段具有以下格式:YYYY-MM-DD HH:MM:SS.S'
另外两个字段应为以下格式:
- DDMMM
- HHMMT,其中T是am的'A',pm的'P'
示例:如果字段中的数据是2008-10-12 13:19:12.0,则提取的字段应包含:
- 12OCT
- 0119P
我尝试使用CONVERT字符串格式,但是没有一种格式与我想要的输出匹配。我正在考虑通过CONVERT提取现场数据,然后使用REPLACE,但是我肯定需要一些帮助,因为我不知道。
任何人都可以精通存储过程帮助我在这里?
谢谢!
如果dt是您的datetime列,那么
对于1:
SUBSTRING(CONVERT(varchar,dt,13),1,2)
+ UPPER(SUBSTRING(CONVERT(varchar,dt,13),4,3))
对于2 :
SUBSTRING(CONVERT(varchar,dt,100),13,2)
+ SUBSTRING(CONVERT(varchar ,dt,100),16,3)
I am trying to write a stored procedure which selects columns from a table and adds 2 extra columns to the ResultSet. These 2 extra columns are the result of conversions on a field in the table which is a Datetime field.
The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S'
The 2 additional fields which should be in the following format:
- DDMMM
- HHMMT, where T is 'A' for a.m. and 'P' for p.m.
Example: If the data in the field was '2008-10-12 13:19:12.0' then the extracted fields should contain:
- 12OCT
- 0119P
I have tried using CONVERT string formats, but none of the formats match the output I want to get. I am thinking along the lines of extracting the field data via CONVERT and then using REPLACE, but I surely need some help here, as I am no sure.
Could anyone well versed in stored procedures help me out here? Thanks!
If dt is your datetime column, then
For 1:
SUBSTRING(CONVERT(varchar, dt, 13), 1, 2)
+ UPPER(SUBSTRING(CONVERT(varchar, dt, 13), 4, 3))
For 2:
SUBSTRING(CONVERT(varchar, dt, 100), 13, 2)
+ SUBSTRING(CONVERT(varchar, dt, 100), 16, 3)
这篇关于SQL Server中的自定义日期/时间格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!