如何在sql server 2008中将180897转换为18 / aug / 1997 [英] How to convert 180897 to 18/aug/1997 in sql server 2008

问题描述

我有一个出生日期字段，其值为180897,150399等，我想将它们转换为18 / aug / 1997,15 / mar / 1999 ..

I have a date of birth field which have values like 180897, 150399 etc and i want to convert them as like 18/aug/1997 , 15/mar/1999 ..

推荐答案

试试这个：

Try This:

DECLARE @input varchar(8), @newDate varchar(10);
SET @input  = '180897'
 
SET @newDate   = LEFT(@input  , 2) + '/' + SUBSTRING(@input, 3,2) + '/' + RIGHT(@input, 2)
SELECT convert(nvarchar,convert(datetime, @newDate , 5),106)

首先获取值作为日期：

First get the values as dates:

DECLARE @input_string varchar(8)
SET @input_string = '180897' -- here goes your date

DECLARE @new_string varchar(10)
SET @new_string = LEFT(@input_string, 2) + '/' + SUBSTRING(@input_string, 3,2) + '/' + RIGHT(@input_string, 2)

DECLARE @new_date DATETIME

SET @new_date = CONVERT(DATETIME, @new_string, 3) -- 3 represents dd/mm/yy, you can adapt if you change "." to some other separator

- 这里是链接 [ ^ ]。



然后你重新回到你想要的字符串：

- here is the link[^].

Then you re-cast back to string you want:

SET @new_string = DAY(@new_date) + '/' + LEFT(DATENAME(MONTH,@new_date),3) +'/' + YEAR(@new_date) -- you might need to cast day and year into varchar again.

轻微的简化是为了节省varchar解析时的值而不是创建datetime对象并使用DATENAME LEFT（MONTH，middle_part_from_above），3）





如果这有帮助请花点时间接受解决方案。

Slight simplyfication is to save varchar values while parsing instead of creating datetime object and use DATENAME LEFT(MONTH, middle_part_from_above), 3)


If this helps please take time to accept the solution.

这篇关于如何在sql server 2008中将180897转换为18 / aug / 1997的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！