使用TSQL将字符串中的dd / mm / yyyy转换为日期格式

I am writing an SQL query using SSMS where one column will return a date. However the date may come from a couple of possible sources controlled with the use of a CASE expression.

我正在使用SSMS编写SQL查询，其中一列将返回日期。但是，日期可能来自使用CASE表达式控制的几个可能的来源。

The first column is of datatype varchar(8) and is successfully converted to a date using

第一列是数据类型varchar（8），并使用成功转换为日期

FORMAT(CAST(tlUserField1 as date),'dd/MM/yyyy')

However my second column is varchar(30) and when using the same option I get the following error:

但是我的第二列是varchar（30），当使用相同的选项时，我收到以下错误：

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

从字符串转换日期和/或时间时，消息241，级别16，状态1，行1转换失败。

I then tried to use

然后我尝试使用

CONVERT (datetime2, tlUserField1, 103)

which returns the same error (I have also tried date and datetime with the same result)

返回相同的错误（我也尝试过相同的日期和日期时间）

It should be noted (not sure if this effects the outcome) that the first column is formatted as YYYYMMDD and the second field is DD/MM/YYYY

应该注意（不确定这是否影响结果）第一列格式为YYYYMMDD，第二列是DD / MM / YYYY

Any assistance would be greatly appreciated.

任何帮助将不胜感激。

1 个解决方案

#1

If I understand you correct then you have a field that can have a date stored as varchar either like '19/07/2017' or like '20170719'

如果我理解你是正确的那么你有一个字段，可以将日期存储为varchar，如'19 / 07/2017'或像'20170719'

To convert a varchar field to date (not recommended using right column type is better) you can use the convert function. In the convert function you can add a parameter to tell the Convert function what format to expect for the convert.

要将varchar字段转换为日期（不建议使用右列类型更好），您可以使用convert函数。在convert函数中，您可以添加一个参数来告诉Convert函数转换所期望的格式。

More info about this function can be found here https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

有关此功能的更多信息，请访问https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

Basic it looks like this :

基本它看起来像这样：

convert(date, datefield, 103)

103 is the code for British/French (format dd/MM/yyyy)

103是英国/法国的代码（格式dd / MM / yyyy）

Now look at these examples

现在看看这些例子

declare @date varchar(30) = '20170719'

select case 
  when charindex('/', @date) > 0 then convert(date, @date, 103) 
  else convert(date, @date, 112) 
end as ConvertedDate

second example:

第二个例子：

declare @date varchar(30) = '19/07/2017'

select case 
  when charindex('/', @date) > 0 then convert(date, @date, 103) 
  else convert(date, @date, 112) 
end as ConvertedDate

Both will succesfully convert the varchar into a date.
Notice that I enter a different format in the @date field each time, and in the select I first determine what format to use (is there a / in the value or not) and then use the correct value for the format parameter.

两者都会成功将varchar转换为日期。请注意，我每次在@date字段中输入不同的格式，并且在select I中首先确定要使用的格式（是否存在值/），然后使用format参数的正确值。

This is however not full proof offcourse since you never know what value can be in the varchar field.

然而，这不是完全证据，因为你永远不知道varchar字段中可以有什么值。

EDIT:

编辑：

The format how the date is shown is not depending on above queries. That depends on settings of your database.
If you want to always show it as dd/MM/yyyy you can use the format function.

显示日期的格式不依赖于上述查询。这取决于您的数据库的设置。如果您想始终将其显示为dd / MM / yyyy，您可以使用格式化功能。

Example :

示例：

select format(getdate(), 'dd/MM/yyyy')

returns for today:

今天的回报：

19/07/2017

in my example it would than be

在我的例子中它不会

declare @date varchar(30) = '20170719'

select format( case 
                 when charindex('/', @date) > 0 then convert(date, @date, 103) 
                 else convert(date, @date, 112) 
               end,
               'dd/MM/yyyy') as ConvertedDate

#1