需要以正确的日期时间格式转换nvarchar SQL [英] Need to convert nvarchar in correct datetime format SQL

问题描述

你好，



再次需要你的帮助。

我当前试图以正确的日期时间格式转换nvarchar值。在SQL Local_time中有一个列是nvarchar，它的值如1160119104041，如果我将其分解，它将像'1''yy''mm''dd''hh''mm'sss'。

我要显示的内容如2016-01-01 10:40:41。



请注意，在nvarchar值中，额外的1可用i想要在显示正确的日期时间时删除它。



我真的很感谢你的帮助并期待它。



我尝试了什么：



搜索网络和谷歌但我找到了解决方案，例如你如何将nvarchar转换为日期时间但没有格式化

Hello there,

Need your help again.
I am curre tly trying to convert an nvarchar value in correct datetime format. There is a column in SQL Local_time it is nvarchar and it has values like 1160119104041, if i break this down it will be like '1' 'yy' 'mm' 'dd' 'hh' 'mm' ss'.
What i want to display is like 2016-01-01 10:40:41.

Note that in nvarchar value an extra 1 is available i want to remove it when displaying the correct datetime.

I would be really thankful for your help and looking forward to it.

What I have tried:

Searched the web and google but i have found solutions like how yo convert nvarchar to datetime but not with formatting

推荐答案

您可以使用 LEFT（）和 RIGHT（ ）这样做。

示例：

You can use LEFT() and RIGHT() to do so.
Example:

declare @test varchar(30)
set @test='1160119104041'

select '20'+left(right(@test,LEN(@test)-1),2)+'-'+left(right(@test,LEN(@test)-3),2)+'-'+left(right(@test,LEN(@test)-5),2)+' '+left(right(@test,LEN(@test)-7),2)+':'+left(right(@test,LEN(@test)-9),2)+':'+left(right(@test,LEN(@test)-11),2)

您可能希望使用 CAST（expressio AS DATETIME）将其投射到 DATETIME 。



注意：这只是一种方式。您可能仍然会寻找更好的选择或建议。

您可能还需要思考，如果年份是98而您希望它是1998年会怎么样！



希望，它有帮助:)

---

更新

您可以创建一个功能喜欢 -

You may want to cast it to DATETIME using CAST(expressio AS DATETIME).

Note: this is just a way. You may still look for better options or suggestions.
You may also need to think, what if year is 98 and you want it to be 1998!

Hope, it helps :)

---

Update
You can create a function like -

CREATE FUNCTION GetDateFromVarchar
(
	@InputValue VARCHAR(30)
)
RETURNS DATETIME
AS
BEGIN
	DECLARE @ReturnValue VARCHAR(30)

	SELECT @ReturnValue= '20'+LEFT(RIGHT(@InputValue,LEN(@InputValue)-1),2)+'-'+LEFT(RIGHT(@InputValue,LEN(@InputValue)-3),2)+'-'+LEFT(RIGHT(@InputValue,LEN(@InputValue)-5),2)+' '+LEFT(RIGHT(@InputValue,LEN(@InputValue)-7),2)+':'+LEFT(RIGHT(@InputValue,LEN(@InputValue)-9),2)+':'+LEFT(RIGHT(@InputValue,LEN(@InputValue)-11),2)

	RETURN CAST(@ReturnValue AS DATETIME)

END
GO

注意：不建议复制粘贴代码。根据您的需要检查和修改。我还没有测试过这个但是应该可以工作。

Note: It not advised to copy paste code. Check and modify according to your need. I haven't tested this yet but should work.

0）什么样的白痴设计一个SQL数据库来将日期时间存储为nvarchar？



1）你要删除哪个1？在开始时还是在结束时？你甚至知道额外的1的实际目的是什么（为什么它在那里）？



2）你的nvarchar字符串的显示方式，没有办法将字符串解析为日期时间，并确保年份是准确的，因为它不是一个4位数的年份。

0) What kind of idiot designs a SQL database to store a datetime as a nvarchar?

1) Which "1" is the one you want to strip? At the beginning, or at the end? Do you even know what the actual purpose of the extra "1" is (why it's there)?

2) The way your nvarchar string is shown, there's no way to parse the string into a datetime with any kind of confidence that the year is accurate because it's not a 4-digit year.

这篇关于需要以正确的日期时间格式转换nvarchar SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！