使用TSQL将字符串中的dd/mm/yyyy转换为日期格式 [英] Convert dd/mm/yyyy in String to Date format using TSQL

问题描述

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

第一列的数据类型为 varchar(8)，并使用

成功将其转换为日期

  FORMAT(CAST(tlUserField1作为日期)，'dd/MM/yyyy') 

但是我的第二列是 varchar(30)，当使用相同的选项时，出现以下错误:

第241层状态1的消息1行
从字符串转换日期和/或时间时转换失败.

然后我尝试使用

  CONVERT(datetime2，tlUserField1，103) 

返回相同的错误(我也尝试过 date 和 datetime 并得到相同的结果)

应注意(不确定是否会影响结果)，第一列的格式为 YYYYMMDD ，第二字段为 DD/MM/YYYY

任何帮助将不胜感激.

解决方案

如果我理解您说的没错，那么您有一个字段可以将日期存储为varchar，例如 '19/07/2017'或类似'20170719'

要将varchar字段转换为日期(不建议使用右列类型更好)，可以使用 convert 函数.在convert函数中，您可以添加一个参数，以告诉Convert函数期望转换的格式是什么.

有关此功能的更多信息，请参见此处 https://docs.microsoft.com/zh-cn/sql/t-sql/functions/cast-and-convert-transact-sql

基本看起来像这样:

 转换(日期，日期字段，103) 

103是英国/法国的代码(格式dd/MM/yyyy)

现在看看这些例子

 声明@date varchar(30)='20170719'选择案例当charindex('/'，@date)>0然后转换(date，@date，103)否则convert(date，@date，112)以ConvertedDate结尾 

第二个示例:

 声明@date varchar(30)= '19/07/2017'选择案例当charindex('/'，@date)>0然后转换(date，@date，103)否则convert(date，@date，112)以ConvertedDate结尾 

两者都将成功地将varchar转换为日期.
请注意，我每次都在@date字段中输入不同的格式，然后在选择中首先确定要使用的格式(值中是否有/)，然后为format参数使用正确的值.

但这并不是完全证明，因为您永远都不知道varchar字段中的值是什么.

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

示例:

 选择格式(getdate()，'dd/MM/yyyy') 

今天返回:

  2017年7月19日 

在我的示例中会是

 声明@date varchar(30)='20170719'选择格式(大小写当charindex('/'，@date)>0然后转换(date，@date，103)否则convert(date，@date，112)结尾，'dd/MM/yyyy')为ConvertedDate 

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.

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

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:

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

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

Any assistance would be greatly appreciated.

解决方案

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'

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.

More info about this function can be found here 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)

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.

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

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.

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

这篇关于使用TSQL将字符串中的dd/mm/yyyy转换为日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！