SQL Server 中的自定义日期/时间格式

2021-12-11 00:00:00 datetime sql sql-server stored-procedures sql-convert

我正在尝试编写一个存储过程，该过程从表中选择列并将 2 个额外的列添加到 ResultSet.这 2 个额外的列是表中一个字段的转换结果，该字段是一个日期时间字段.

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.

日期时间格式字段具有以下格式'YYYY-MM-DD HH:MM:SS.S'

The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S'

应采用以下格式的 2 个附加字段:

The 2 additional fields which should be in the following format:

  1. DDMMM
  2. HHMMT，其中 T 是上午的A"和下午的P"

示例:如果字段中的数据为2008-10-12 13:19:12.0"，则提取的字段应包含:

Example: If the data in the field was '2008-10-12 13:19:12.0' then the extracted fields should contain:

  1. 12OCT
  2. 0119P

我尝试使用 CONVERT 字符串格式，但没有一种格式与我想要的输出相匹配.我正在考虑通过 CONVERT 提取字段数据然后使用 REPLACE，但我在这里肯定需要一些帮助，因为我不确定.

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!

推荐答案

如果 dt 是您的日期时间列，则

If dt is your datetime column, then

对于 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)

相关文章