在sql server中使用分隔符将没有分隔符的日期和时间转换为日期时间

I would like to convert date(without any delimiter ex. 31012015) and time(without any delimiter ex.0144) to date and time with delimiter. i.e.

我想将日期(没有任何分隔符,例如31012015)和时间(没有任何分隔符ex.0144)转换为带分隔符的日期和时间。即

31012015 => 31/01/2015 (dd/mm/yyyy)

31012015 => 31/01/2015(年/月/日)

0144 => 01:44 (hh:mm)

0144 => 01:44(hh:mm)

I tried various option in sql server (using various date-time format) but couldn't able to find out its solution.

我在sql server中尝试了各种选项(使用各种日期时间格式),但无法找到它的解决方案。

2 个解决方案

#1

Since you only seem to care about the string representation of the value, you could use STUFF:

由于您似乎只关心值的字符串表示,您可以使用STUFF:

SELECT STUFF(@time, 3, 0, ':') as NewTime,
  STUFF(STUFF(@date, 3, 0, '/'), 6, 0, '/') as NewDate

#2

try this

select replace(CONVERT(VARCHAR(10), GETDATE(), 103),'/','') as date_format, 
left(replace(CONVERT(VARCHAR(10), GETDATE(), 108),':',''),4) timeformat