当前位置： 文江博客话题详情

Sql Server 字符串到日期的转换

发布于 2024-07-07 13:11:24 字数 491 浏览 10 评论 0原文

我想将这样的字符串转换

'10/15/2008 10:06:32 PM'

为 Sql Server 中的等效 DATETIME 值。

在Oracle中，我会这样说：

TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM')

这个问题意味着我必须解析字符串转换为标准格式之一，然后使用其中一种进行转换这些代码。 对于这样一个平凡的操作来说，这似乎很荒唐。 有更容易的方法吗？

原文

I want to convert a string like this:

'10/15/2008 10:06:32 PM'

into the equivalent DATETIME value in Sql Server.

In Oracle, I would say this:

TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM')

This question implies that I must parse the string into one of the standard formats, and then convert using one of those codes. That seems ludicrous for such a mundane operation. Is there an easier way?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（17）

我的鱼塘能养鲲 2024-07-14 13:11:24

尝试此操作

Cast('7/7/2011' as datetime)

并

Convert(DATETIME, '7/7/2011', 101)

参阅 CAST 和 CONVERT (Transact-SQL) 了解更多详情。

Try this

Cast('7/7/2011' as datetime)

and

Convert(DATETIME, '7/7/2011', 101)

See CAST and CONVERT (Transact-SQL) for more details.

回复 原文
向日葵 2024-07-14 13:11:24

通过查询处理器运行它。 它像这样格式化日期和/或时间，其中之一应该可以为您提供您正在寻找的内容。 适应起来并不困难：

Declare @d datetime
select @d = getdate()

select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,12),12,'yymmdd'
union all
select @d,convert(varchar,@d,112),112,'yyyymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'

Run this through your query processor. It formats dates and/or times like so and one of these should give you what you're looking for. It wont be hard to adapt:

Declare @d datetime
select @d = getdate()

select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,12),12,'yymmdd'
union all
select @d,convert(varchar,@d,112),112,'yyyymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'
回复 原文
删除→记忆 2024-07-14 13:11:24

在 SQL Server Denali 中，您将能够做一些接近您正在寻找的事情。 但是您仍然不能只传递任何任意定义的古怪日期字符串并期望 SQL Server 能够适应。 这是一个使用您在自己的答案中发布的内容的示例。 FORMAT() 函数还可以接受语言环境作为可选参数 - 它基于 .Net 的格式，因此您期望看到的大多数（如果不是全部）令牌格式都会在那里。

DECLARE @d DATETIME = '2008-10-13 18:45:19';

-- returns Oct-13/2008 18:45:19:
SELECT FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss');

-- returns NULL if the conversion fails:
SELECT TRY_PARSE(FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss') AS DATETIME);

-- returns an error if the conversion fails:
SELECT PARSE(FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss') AS DATETIME);

我强烈建议您加强控制并清理您的日期输入。 让人们使用任何他们想要的格式在自由文本表单字段中输入日期的日子现在应该已经过去了。 如果有人输入 8/9/2011 是 8 月 9 日还是 9 月 8 日？ 如果您让他们在日历控件上选择日期，则应用程序可以控制格式。 无论您如何尝试预测用户的行为，他们总是会想出一种更愚蠢的方式来输入您没有计划的日期。

不过，在 Denali 之前，我认为 @Ovidiu 提供了迄今为止最好的建议...通过实现您自己的 CLR 函数，这可以变得相当简单。 然后，您可以根据需要为任意数量的古怪非标准格式编写案例/开关。

@dhergert 的更新：

SELECT TRY_PARSE('10/15/2008 10:06:32 PM' AS DATETIME USING 'en-us');
SELECT TRY_PARSE('15/10/2008 10:06:32 PM' AS DATETIME USING 'en-gb');

结果：

2008-10-15 22:06:32.000
2008-10-15 22:06:32.000

您仍然需要首先获得其他关键信息。 您无法使用本机 T-SQL 确定 6/9/2012 是 6 月 9 日还是 9 月 6 日。

In SQL Server Denali, you will be able to do something that approaches what you're looking for. But you still can't just pass any arbitrarily defined wacky date string and expect SQL Server to accommodate. Here is one example using something you posted in your own answer. The FORMAT() function and can also accept locales as an optional argument - it is based on .Net's format, so most if not all of the token formats you'd expect to see will be there.

DECLARE @d DATETIME = '2008-10-13 18:45:19';

-- returns Oct-13/2008 18:45:19:
SELECT FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss');

-- returns NULL if the conversion fails:
SELECT TRY_PARSE(FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss') AS DATETIME);

-- returns an error if the conversion fails:
SELECT PARSE(FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss') AS DATETIME);

I strongly encourage you to take more control and sanitize your date inputs. The days of letting people type dates using whatever format they want into a freetext form field should be way behind us by now. If someone enters 8/9/2011 is that August 9th or September 8th? If you make them pick a date on a calendar control, then the app can control the format. No matter how much you try to predict your users' behavior, they'll always figure out a dumber way to enter a date that you didn't plan for.

Until Denali, though, I think that @Ovidiu has the best advice so far... this can be made fairly trivial by implementing your own CLR function. Then you can write a case/switch for as many wacky non-standard formats as you want.

UPDATE for @dhergert:

SELECT TRY_PARSE('10/15/2008 10:06:32 PM' AS DATETIME USING 'en-us');
SELECT TRY_PARSE('15/10/2008 10:06:32 PM' AS DATETIME USING 'en-gb');

Results:

2008-10-15 22:06:32.000
2008-10-15 22:06:32.000

You still need to have that other crucial piece of information first. You can't use native T-SQL to determine whether 6/9/2012 is June 9th or September 6th.

回复 原文
眼中杀气 2024-07-14 13:11:24

SQL Server（2005、2000、7.0）没有任何灵活甚至不灵活的方法来获取字符串格式的任意结构化日期时间并将其转换为日期时间数据类型。

我所说的“任意”是指“编写它的人（尽管可能不是你、我或地球另一端的人）会认为是直观且完全显而易见的形式。” 坦率地说，我不确定是否有这样的算法。

SQL Server (2005, 2000, 7.0) does not have any flexible, or even non-flexible, way of taking an arbitrarily structured datetime in string format and converting it to the datetime data type.

By "arbitrarily", I mean "a form that the person who wrote it, though perhaps not you or I or someone on the other side of the planet, would consider to be intuitive and completely obvious." Frankly, I'm not sure there is any such algorithm.

回复 原文
紫南 2024-07-14 13:11:24

使用此：

SELECT convert(datetime, '2018-10-25 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm

并参考 官方文档中的表格 为转换代码。

Use this:

SELECT convert(datetime, '2018-10-25 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm

And refer to the table in the official documentation for the conversion codes.

回复 原文
踏月而来 2024-07-14 13:11:24

对于这个问题，我使用的最佳解决方案是在 Sql Server 2005 中使用 CLR 函数，该函数使用 DateTime.Parse 或 ParseExact 函数之一返回具有指定格式的 DateTime 值。

For this problem the best solution I use is to have a CLR function in Sql Server 2005 that uses one of DateTime.Parse or ParseExact function to return the DateTime value with a specified format.

回复 原文
寂寞清仓 2024-07-14 13:11:24

简短回答：

SELECT convert(date, '10/15/2011 00:00:00', 101) as [MM/dd/YYYY]

其他日期格式可以在 SQL Server 帮助程序 > SQL Server 日期格式

Short answer:

SELECT convert(date, '10/15/2011 00:00:00', 101) as [MM/dd/YYYY]

Other date formats can be found at SQL Server Helper > SQL Server Date Formats

回复 原文
甜宝宝 2024-07-14 13:11:24

我花了一分钟才弄清楚这一点，以防万一它可能对某人有所帮助：

在 SQL Server 2012 及更好的版本中，您可以使用此函数：

SELECT DATEFROMPARTS(2013, 8, 19);

以下是我最终提取日期部分以放入此函数中的方法：

select
DATEFROMPARTS(right(cms.projectedInstallDate,4),left(cms.ProjectedInstallDate,2),right( left(cms.ProjectedInstallDate,5),2)) as 'dateFromParts'
from MyTable

Took me a minute to figure this out so here it is in case it might help someone:

In SQL Server 2012 and better you can use this function:

SELECT DATEFROMPARTS(2013, 8, 19);

Here's how I ended up extracting the parts of the date to put into this function:

select
DATEFROMPARTS(right(cms.projectedInstallDate,4),left(cms.ProjectedInstallDate,2),right( left(cms.ProjectedInstallDate,5),2)) as 'dateFromParts'
from MyTable
回复 原文
嗳卜坏 2024-07-14 13:11:24

这里获得最多支持的答案是 guravg's 和 Taptronic 的。 不过，我想做出一项贡献。

它们显示的从 0 到 131 的具体格式数字可能会有所不同，具体取决于您的用例（请参阅此处为完整数字列表），输入数字可以是不确定的one，这意味着一个 SQL SERVER 实例与另一个 SQL SERVER 实例的预期结果日期不一致，请避免使用强制转换 a出于同样的原因，字符串方法。

从 SQL Server 2005 及其兼容级别 90 开始，
隐式日期转换变得不确定。 日期转换
开始依赖于 SET LANGUAGE 和 SET DATEFORMAT
90级。

非确定性值为 0-100、106、107、109、113< /代码>，<代码>130。 这可能会导致错误。

最好的选择是坚持确定性设置，我当前的偏好是 ISO 格式（12、112、23、126），因为它们似乎是 IT 人员用例的最标准。

Convert(varchar(30), '210510', 12)                   -- yymmdd
Convert(varchar(30), '20210510', 112)                -- yyyymmdd
Convert(varchar(30), '2021-05-10', 23)               -- yyyy-mm-dd
Convert(varchar(30), '2021-05-10T17:01:33.777', 126) -- yyyy-mm-ddThh:mi:ss.mmm (no spaces)

The most upvoted answer here are guravg's and Taptronic's. However, there's one contribution I'd like to make.

The specific format number they showed from 0 to 131 may vary depending on your use-case (see full number list here), the input number can be a nondeterministic one, which means that the expected result date isn't consistent from one SQL SERVER INSTANCE to another, avoid using the cast a string approach for the same reason.

Starting with SQL Server 2005 and its compatibility level of 90,
implicit date conversions became nondeterministic. Date conversions
became dependent on SET LANGUAGE and SET DATEFORMAT starting with
level 90.

Non deterministic values are 0-100, 106, 107, 109, 113, 130. which may result in errors.

The best option is to stick to a deterministic setting, my current preference are ISO formats (12, 112, 23, 126), as they seem to be the most standard for IT people use cases.

Convert(varchar(30), '210510', 12)                   -- yymmdd
Convert(varchar(30), '20210510', 112)                -- yyyymmdd
Convert(varchar(30), '2021-05-10', 23)               -- yyyy-mm-dd
Convert(varchar(30), '2021-05-10T17:01:33.777', 126) -- yyyy-mm-ddThh:mi:ss.mmm (no spaces)
回复 原文
小帐篷 2024-07-14 13:11:24

此页面提供了所有CONVERT 函数可用的指定日期时间转换。 如果您的值不属于可接受的模式之一，那么我认为最好的办法是采用 ParseExact 路线。

This page has some references for all of the specified datetime conversions available to the CONVERT function. If your values don't fall into one of the acceptable patterns, then I think the best thing is to go the ParseExact route.

回复 原文
卸妝后依然美 2024-07-14 13:11:24

就个人而言，如果您处理任意或完全不规则的格式，只要您提前知道它们是什么或将是什么，那么只需使用正则表达式来提取您想要的日期部分并形成有效的日期/日期时间组件。

Personally if your dealing with arbitrary or totally off the wall formats, provided you know what they are ahead of time or are going to be then simply use regexp to pull the sections of the date you want and form a valid date/datetime component.

回复 原文
丑疤怪 2024-07-14 13:11:24

如果你想让 SQL Server 尝试找出答案，只需使用 CAST
CAST('随便' AS 日期时间)
然而，这通常是一个坏主意。 可能会出现国际日期的问题。 正如您所发现的，为了避免这些问题，您需要使用日期的 ODBC 规范格式。 即格式编号 120，20 是仅两位数年份的格式。
我不认为 SQL Server 具有允许您提供用户给定格式的内置函数。 您可以自己编写，如果在线搜索甚至可能找到一个。

If you want SQL Server to try and figure it out, just use CAST
CAST('whatever' AS datetime)
However that is a bad idea in general. There are issues with international dates that would come up. So as you've found, to avoid those issues, you want to use the ODBC canonical format of the date. That is format number 120, 20 is the format for just two digit years.
I don't think SQL Server has a built-in function that allows you to provide a user given format. You can write your own and might even find one if you search online.

回复 原文
绝情姑娘 2024-07-14 13:11:24

将 MSSQL 中的字符串隐式转换为日期时间

create table tmp 
(
  ENTRYDATETIME datetime
);

insert into tmp (ENTRYDATETIME) values (getdate());
insert into tmp (ENTRYDATETIME) values ('20190101');  --convert string 'yyyymmdd' to datetime


select * from tmp where ENTRYDATETIME > '20190925'  --yyyymmdd 
select * from tmp where ENTRYDATETIME > '20190925 12:11:09.555'--yyyymmdd HH:MIN:SS:MS

convert string to datetime in MSSQL implicitly

create table tmp 
(
  ENTRYDATETIME datetime
);

insert into tmp (ENTRYDATETIME) values (getdate());
insert into tmp (ENTRYDATETIME) values ('20190101');  --convert string 'yyyymmdd' to datetime


select * from tmp where ENTRYDATETIME > '20190925'  --yyyymmdd 
select * from tmp where ENTRYDATETIME > '20190925 12:11:09.555'--yyyymmdd HH:MIN:SS:MS
回复 原文
本王不退位尔等都是臣 2024-07-14 13:11:24

您可以使用此代码轻松实现此目的。

选择转换（日期时间，转换（varchar（30），'10/15/2008 10:06:32 PM',102）,102）

You can easily achieve this by using this code.

SELECT Convert(datetime, Convert(varchar(30),'10/15/2008 10:06:32 PM',102),102)

回复 原文
烟─花易冷 2024-07-14 13:11:24

我发现这篇文章对于创建将字符串值从 .NET Datetime.ToString(...) 转换回 SQL 日期时间的函数很有用。 在我的系统中对数千个字符串日期进行了测试。 看起来效果不错。 希望这可以节省您的时间。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Julian King
-- Create date: 3/14/2023
-- Description: Parses and returns a date from a variety of string formats
-- See: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/
--      Thu Jul 01 2021 00:00:00 GMT-0400 (Eastern Daylight Time)
--      2018-04-16T00:00:00
--      2021-05-04T20:13:34.897781
--      2021-11-04 20:58:57.352836
--      4/17/2020 11:02:59 AM
--      January 13, 2020
-- =============================================
CREATE FUNCTION dbo.DateConvert
(
    @datestring VARCHAR(55)
)
RETURNS DATETIME
AS
BEGIN
    -- Declare the return variable here
    SET @datestring = RTRIM(LTRIM(@datestring))

    IF @datestring IS NULL
        RETURN CAST('1753-1-1' AS DATETIME)  
    
    IF @datestring = ''
        RETURN CAST('1753-1-1' AS DATETIME)

    -- Thu Jul 01 2021 00:00:00 GMT-0400 (Eastern Daylight Time)
    IF LEN(@datestring) > 30 AND @datestring LIKE '%GMT-%'
        SET @datestring = RTRIM(LTRIM(SUBSTRING(@datestring,5,20)))


    -- 2021-05-04T20:13:34.897781
    -- 2020-11-01T00:00:00
    IF @datestring LIKE '%T%'
        SET @datestring = REPLACE(@datestring,'T',' ')

    -- 2021-05-04 20:13:34.897781
    IF LEN(@datestring) = 26
        SET @datestring = SUBSTRING(@datestring,0,20)
    
    IF TRY_CONVERT(DATETIME,@datestring,101) IS NULL 
        RETURN CAST('1753-1-1' AS DATETIME) 
    ELSE
        RETURN CONVERT(DATETIME,@datestring,101) 

    RETURN CAST('1753-1-1' AS DATETIME)  
END
GO

I found this article useful in creating my function that converts string values from .NET Datetime.ToString(...) back into SQL datetime. Tested it on 1000s of string dates in my system. Seems to work well. Hope this saves you time.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Julian King
-- Create date: 3/14/2023
-- Description: Parses and returns a date from a variety of string formats
-- See: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/
--      Thu Jul 01 2021 00:00:00 GMT-0400 (Eastern Daylight Time)
--      2018-04-16T00:00:00
--      2021-05-04T20:13:34.897781
--      2021-11-04 20:58:57.352836
--      4/17/2020 11:02:59 AM
--      January 13, 2020
-- =============================================
CREATE FUNCTION dbo.DateConvert
(
    @datestring VARCHAR(55)
)
RETURNS DATETIME
AS
BEGIN
    -- Declare the return variable here
    SET @datestring = RTRIM(LTRIM(@datestring))

    IF @datestring IS NULL
        RETURN CAST('1753-1-1' AS DATETIME)  
    
    IF @datestring = ''
        RETURN CAST('1753-1-1' AS DATETIME)

    -- Thu Jul 01 2021 00:00:00 GMT-0400 (Eastern Daylight Time)
    IF LEN(@datestring) > 30 AND @datestring LIKE '%GMT-%'
        SET @datestring = RTRIM(LTRIM(SUBSTRING(@datestring,5,20)))


    -- 2021-05-04T20:13:34.897781
    -- 2020-11-01T00:00:00
    IF @datestring LIKE '%T%'
        SET @datestring = REPLACE(@datestring,'T',' ')

    -- 2021-05-04 20:13:34.897781
    IF LEN(@datestring) = 26
        SET @datestring = SUBSTRING(@datestring,0,20)
    
    IF TRY_CONVERT(DATETIME,@datestring,101) IS NULL 
        RETURN CAST('1753-1-1' AS DATETIME) 
    ELSE
        RETURN CONVERT(DATETIME,@datestring,101) 

    RETURN CAST('1753-1-1' AS DATETIME)  
END
GO
回复 原文
红衣飘飘貌似仙 2024-07-14 13:11:24

这段代码解决了我的问题：

convert(date,YOUR_DATE,104)

如果您使用 timestamp 您可以使用以下代码：

convert(datetime,YOUR_DATE,104)

This code solve my problem :

convert(date,YOUR_DATE,104)

If you are using timestamp you can you the below code :

convert(datetime,YOUR_DATE,104)
回复 原文
满身野味 2024-07-14 13:11:24
dateadd(day,0,'10/15/2008 10:06:32 PM')

dateadd(day,0,'10/15/2008 10:06:32 PM')
回复 原文
~没有更多了~

关于作者

故事↓在人

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

Promise

文章 0 评论 0

qq_lbRlsh

文章 0 评论 0

待＂谢繁草

文章 0 评论 0

yy2010hell

文章 0 评论 0

漫无边际

文章 0 评论 0

傲娇萝莉攻

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文