entityMap|blocks|key|3n037|text|您可以使用cast(@date+as+datetime)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|a6cam|declare+@date+varchar(max);
++++set+@date='170202';
++++select
++++CASE+WHEN+(ISDATE(cast(@date+as+datetime))+=+1)+
++++++++THEN+CONVERT(VARCHAR(max),+CONVERT(datetime,++cast(@date+as+datetime)),+112)+end
++++++++from+table

++++set+@date='02/02/02';
++++select
++++CASE+WHEN+(ISDATE(cast(@date+as+datetime))+=+1)+
++++++++THEN+CONVERT(VARCHAR(max),+CONVERT(datetime,++cast(@date+as+datetime)),+112)+end
++++++++from+table|code-block|syntax|javascript|64qh7^0|5|N|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|M|9|@$A|N|B|O|C|D]]|E|@]|F|$]]|$2|G|4|H|6|I|8|P|9|@]|E|@]|F|$J|K]]|$2|L|4|-4|6|7|8|Q|9|@]|E|@]|F|$]]]]

You can use <code>cast(@date as datetime)</code> 

<pre><code>declare @date varchar(max);
 set @date='170202';
 select
 CASE WHEN (ISDATE(cast(@date as datetime)) = 1) 
 THEN CONVERT(VARCHAR(max), CONVERT(datetime, cast(@date as datetime)), 112) end
 from table

 set @date='02/02/02';
 select
 CASE WHEN (ISDATE(cast(@date as datetime)) = 1) 
 THEN CONVERT(VARCHAR(max), CONVERT(datetime, cast(@date as datetime)), 112) end
 from table
</code></pre>

entityMap|blocks|key|7vc0i|text|请使用create+function检查dateformat是否有效，并在cash+clouse中使用这个有趣的查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|dagga|​|96l86|ALTER++FUNCTION+dbo.f_CheckDate
(@InDate+nvarchar(25))
RETURNS+DATE
AS
++++BEGIN
++++++++declare+@Return+DATETIME
++++++++select+@return+=+CASE+WHEN+ISDATE(@InDate)+=+1
+++++++++++++++++++++++++++++THEN+++@InDate
++++++++++++++++++++++++++++ELSE+NULL
++++++++++++++++++++++++++++END
++++++++return+@return
++++END|code-block|syntax|javascript|4asd7|qlqj^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|M|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|N|9|@]|A|@]|B|$H|I]]|$2|J|4|D|6|7|8|O|9|@]|A|@]|B|$]]|$2|K|4|-4|6|7|8|P|9|@]|A|@]|B|$]]]]

please use create function for check dateformat is Valid or not and use this fun in your query inside cash clouse.

<div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override"><code>ALTER FUNCTION dbo.f_CheckDate
(@InDate nvarchar(25))
RETURNS DATE
AS
 BEGIN
 declare @Return DATETIME
 select @return = CASE WHEN ISDATE(@InDate) = 1
 THEN @InDate
 ELSE NULL
 END
 return @return
 END</code></pre>
</div>
</div>

entityMap|0|type|LINK|mutability|MUTABLE|data|url|https://msdn.microsoft.com/en-us/library/hh974669.aspx|1|https://msdn.microsoft.com/en-us/library/hh230993.aspx|blocks|key|71ou1|text|你可以使用TRY或TRY，如果值不能转换，它将返回NULL。|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|6scr|SELECT+
++TRY_CAST('20170228'+AS+DATETIME),
++TRY_CAST('170228'+AS+DATETIME),
++TRY_CONVERT(DATETIME,+'20170228'),
++TRY_CONVERT(DATETIME,+'170228')|code-block|syntax|javascript|fr1q7|这适用于SQL+Server+2012及更高版本。|743ll^0|5|3|0|9|3|1|0|0|0^^$0|$1|$2|3|4|5|6|$7|8]]|9|$2|3|4|5|6|$7|A]]]|B|@$C|D|E|F|2|G|H|U|I|@]|J|@$K|V|L|W|C|X]|$K|Y|L|Z|C|10]]|6|$]]|$C|M|E|N|2|O|H|11|I|@]|J|@]|6|$P|Q]]|$C|R|E|S|2|G|H|12|I|@]|J|@]|6|$]]|$C|T|E|-4|2|G|H|13|I|@]|J|@]|6|$]]]]

You could use <a href="https://msdn.microsoft.com/en-us/library/hh974669.aspx" rel="nofollow noreferrer">TRY_CAST</a> or <a href="https://msdn.microsoft.com/en-us/library/hh230993.aspx" rel="nofollow noreferrer">TRY_CONVERT</a> if value cannot be cast it will return NULL.

<pre><code>SELECT 
 TRY_CAST('20170228' AS DATETIME),
 TRY_CAST('170228' AS DATETIME),
 TRY_CONVERT(DATETIME, '20170228'),
 TRY_CONVERT(DATETIME, '170228')
</code></pre>

This works for SQL Server 2012 and newer.

entityMap|0|type|LINK|mutability|MUTABLE|data|url|https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(TRY_CONVERT_TSQL%2529;k(SQL11.SWB.TSQLRESULTS.F1%2529;k(SQL11.SWB.TSQLQUERY.F1%2529;k(MISCELLANEOUSFILESPROJECT%2529;k(DevLang-TSQL%2529&rd=true|1|https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(CAST_TSQL%2529;k(SQL11.SWB.TSQLRESULTS.F1%2529;k(SQL11.SWB.TSQLQUERY.F1%2529;k(MISCELLANEOUSFILESPROJECT%2529;k(DevLang-TSQL%2529&rd=true|blocks|key|2q27l|text|您可以使用TRY_CONVERT(https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(TRY_CONVERT_TSQL%2529;k(SQL11.SWB.TSQLRESULTS.F1%2529;k(SQL11.SWB.TSQLQUERY.F1%2529;k(MISCELLANEOUSFILESPROJECT%2529;k(DevLang-TSQL%2529&rd=true)和CONVERT(https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(CAST_TSQL%2529;k(SQL11.SWB.TSQLRESULTS.F1%2529;k(SQL11.SWB.TSQLQUERY.F1%2529;k(MISCELLANEOUSFILESPROJECT%2529;k(DevLang-TSQL%2529&rd=true)来获取正确的格式并转换值。然后检查字符串是否恰好是6个字符，以防止返回其他格式。|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|10u2e|SELECT
++convert(char(10),convert(date,+theDates,+12),112)
FROM
++(values('02/02/02'),('170202'))+x(theDates)
WHERE
++try_convert(date,+theDates,+12)+is+not+null
++and+len(theDates)+=+6|code-block|syntax|javascript|68md^0|H|5S|0|6J|5L|1|0|0^^$0|$1|$2|3|4|5|6|$7|8]]|9|$2|3|4|5|6|$7|A]]]|B|@$C|D|E|F|2|G|H|S|I|@]|J|@$K|T|L|U|C|V]|$K|W|L|X|C|Y]]|6|$]]|$C|M|E|N|2|O|H|Z|I|@]|J|@]|6|$P|Q]]|$C|R|E|-4|2|G|H|10|I|@]|J|@]|6|$]]]]

You can use <a href="https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&amp;l=EN-US&amp;k=k(TRY_CONVERT_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&amp;rd=true" rel="nofollow noreferrer">TRY_CONVERT</a> and <a href="https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&amp;l=EN-US&amp;k=k(CAST_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&amp;rd=true" rel="nofollow noreferrer">CONVERT</a> to get the correct format and convert the value. Then check that the string is exactly 6 character to prevent other formats from being returned.

<pre><code>SELECT
 convert(char(10),convert(date, theDates, 12),112)
FROM
 (values('02/02/02'),('170202')) x(theDates)
WHERE
 try_convert(date, theDates, 12) is not null
 and len(theDates) = 6
</code></pre>

I have a table with a VARCHAR field called ArrivalDate in format yymmdd (such as 170202).
I am writing a query which converts it to yyyymmdd so it should become 20170202. 

However my problem is that I need to cater for the case when inappropriate data is entered into the field, and my query needs to exclude that data. I am achieving this exclusion by using the ISDATE function of TSQL. I also need to select the least recent entry (I'm using order by asc for this).

I am using a variety of converts to write this query, below is my implementation with a sample table and data.

<pre><code>Declare @tmp TABLE (theDates VARCHAR(MAX))

INSERT INTO @tmp VALUES('170202')

SELECT TOP 1 t.theDates
WHEN (ISDATE(t.theDates) = 1) THEN CONVERT( VARCHAR(max),CONVERT(datetime t.theDates), 112)
FROM @tmp t
WHERE (ISDATE(t.theDates) = 1)
ORDER BY CAST(t.theDates as DATE)
</code></pre>

However I do not like my approach and it occasionally fails conversion and throws an error with values such as 02/02/02 which breaks the query. Can someone please show me a better way of writing this functionality. 

Much appreciated!

Convert VARCHAR in format YYMMDD to YYYYMMDD and ignore invalid date formats

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

 我有一个表，其中包含一个名为ArrivalDate的VARCHAR字段，格式为yymmdd (例如170202)。我正在编写一个查询，将其转换为yyyymmdd，因此它应该变为20170202。 然而，我的问题是，当不适当的数据输入到字段中时，我需要处理这种情况，并且我的查询需要排除这些数据。我通过使用TSQL的IS...

问将格式为YYMMDD的VARCHAR转换为YYYYMMDD并忽略无效的日期格式
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将格式为YYMMDD的VARCHAR转换为YYYYMMDD并忽略无效的日期格式EN