Sql Server строка для преобразования даты
Я хочу преобразовать строку следующим образом:
'10/15/2008 10:06:32 PM'
в эквивалентное значение DATETIME в Sql Server.
В Oracle я бы сказал так:
TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM')
Этот вопрос подразумевает, что я должен проанализировать строку в одном из стандартных форматов, а затем преобразовать, используя один из этих кодов. Это кажется смешным для такой мирской операции. Есть ли более простой способ?
18 ответов
SQL Server (2005, 2000, 7.0) не имеет какого-либо гибкого или даже негибкого способа получения произвольно структурированного datetime в строковом формате и преобразования его в тип данных datetime.
Под "произвольно" я подразумеваю "форму, которую человек, который ее написал, хотя, возможно, не вы, я или кто-то на другой стороне планеты, сочли бы ее интуитивной и совершенно очевидной". Честно говоря, я не уверен, что есть такой алгоритм.
Попробуй это
Cast('7/7/2011' as datetime)
а также
Convert(varchar(30),'7/7/2011',102)
См. CAST и CONVERT (Transact-SQL) для более подробной информации.
Запустите это через ваш процессор запросов. Он форматирует даты и / или время примерно так, и один из них должен дать вам то, что вы ищете. Это не будет трудно адаптировать:
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)'
В 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);
Я настоятельно рекомендую вам взять под контроль и обезопасить ваши данные даты. Дни, позволяющие людям вводить даты в произвольном формате в поле формы свободного текста, уже давно позади. Если кто-то входит 9.09.2011, это 9 августа или 8 сентября? Если вы заставите их выбрать дату в элементе управления календаря, то приложение сможет контролировать формат. Независимо от того, сколько вы пытаетесь предсказать поведение ваших пользователей, они всегда найдут более тупой способ ввода даты, которую вы не планировали.
До Денали, тем не менее, я думаю, что @Ovidiu имеет лучший совет на данный момент... это можно сделать довольно тривиальным, реализовав собственную функцию CLR. Затем вы можете написать case/switch для любого количества нестандартных нестандартных форматов.
ОБНОВЛЕНИЕ для @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 9 июня или 6 сентября.
Использовать этот:
SELECT convert(datetime, '2018-10-25 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
И обратитесь к таблице в официальной документации для преобразования кодов.
Для этой проблемы лучшее решение, которое я использую, - это иметь функцию CLR в Sql Server 2005, которая использует одну из функций DateTime.Parse или ParseExact для возврата значения DateTime в указанном формате.
Почему бы не попробовать
select convert(date,'10/15/2011 00:00:00',104) as [MM/dd/YYYY]
форматы дат можно найти в разделе Помощник по SQL Server> Форматы даты в SQL Server.
У меня ушла минута, чтобы понять это, так что вот оно, на случай, если это может кому-то помочь:
В 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
Я знаю, что это старый злой пост с множеством ответов, но многие люди думают, что им НУЖНО либо разбить вещи на части и собрать их вместе, либо они настаивают на том, что нет способа неявно выполнить преобразование, которое запрашивал оригинал OP.
Чтобы просмотреть и, надеюсь, дать простой ответ другим с тем же вопросом, OP спросил, как преобразовать "15.10.2008 10:06:32 PM" в DATETIME. Теперь у SQL Server есть некоторые языковые зависимости для временных преобразований, но если язык английский или что-то подобное, это становится простой проблемой... просто выполните преобразование и не беспокойтесь о формате. Например (и вы можете использовать CONVERT или CAST)...
SELECT UsingCONVERT = CONVERT(DATETIME,'10/15/2008 10:06:32 PM')
,UsingCAST = CAST('10/15/2008 10:06:32 PM' AS DATETIME)
;
... и это дает следующие ответы, оба из которых верны.
Как говорится в рекламных роликах: "Но подождите! Пока не заказывайте! Без дополнительной платы он может сделать НАМНОГО больше!"
Давайте посмотрим на реальную силу временных преобразований с помощью DATETIME и частично рассмотрим ошибку, известную как DATETIME2. Ознакомьтесь с причудливыми форматами, которые DATETIME может обрабатывать автоматически, а DATETIME2 - нет. Запустите следующий код и посмотрите...
--===== Set the language for this example.
SET LANGUAGE ENGLISH --Same a US-English
;
--===== Use a table constructor as if it were a table for this example.
SELECT *
,DateTimeCONVERT = TRY_CONVERT(DATETIME,StringDT)
,DateTimeCAST = TRY_CAST(StringDT AS DATETIME)
,DateTime2CONVERT = TRY_CONVERT(DATETIME2,StringDT)
,DateTime2CAST = TRY_CAST(StringDT AS DATETIME2)
FROM (
VALUES
('Same Format As In The OP' ,'12/16/2001 01:51:01 PM')
,('Almost Normal' ,'16 December, 2001 1:51:01 PM')
,('More Normal' ,'December 16, 2001 01:51:01 PM')
,('Time Up Front + Spaces' ,' 13:51:01 16 December 2001')
,('Totally Whacky Format #01' ,' 16 13:51:01 December 2001')
,('Totally Whacky Format #02' ,' 16 December 13:51:01 2001 ')
,('Totally Whacky Format #03' ,' 16 December 01:51:01 PM 2001 ')
,('Totally Whacky Format #04' ,' 2001 16 December 01:51:01 PM ')
,('Totally Whacky Format #05' ,' 2001 December 01:51:01 PM 16 ')
,('Totally Whacky Format #06' ,' 2001 16 December 01:51:01 PM ')
,('Totally Whacky Format #07' ,' 2001 16 December 13:51:01 PM ')
,('Totally Whacky Format #08' ,' 2001 16 13:51:01 PM December ')
,('Totally Whacky Format #09' ,' 13:51:01 PM 2001.12/16 ')
,('Totally Whacky Format #10' ,' 13:51:01 PM 2001.December/16 ')
,('Totally Whacky Format #11' ,' 13:51:01 PM 2001.Dec/16 ')
,('Totally Whacky Format #12' ,' 13:51:01 PM 2001.Dec.16 ')
,('Totally Whacky Format #13' ,' 13:51:01 PM 2001/Dec.16')
,('Totally Whacky Format #14' ,' 13:51:01 PM 2001 . 12/16 ')
,('Totally Whacky Format #15' ,' 13:51:01 PM 2001 . December / 16 ')
,('Totally Whacky Format #16' ,' 13:51:01 PM 2001 . Dec / 16 ')
,('Totally Whacky Format #17' ,' 13:51:01 PM 2001 . Dec . 16 ')
,('Totally Whacky Format #18' ,' 13:51:01 PM 2001 / Dec . 16')
,('Totally Whacky Format #19' ,' 13:51:01 PM 2001 . Dec - 16 ')
,('Totally Whacky Format #20' ,' 13:51:01 PM 2001 - Dec - 16 ')
,('Totally Whacky Format #21' ,' 13:51:01 PM 2001 - Dec . 16')
,('Totally Whacky Format #22' ,' 13:51:01 PM 2001 - Dec / 16 ')
,('Totally Whacky Format #23' ,' 13:51:01 PM 2001 / Dec - 16')
,('Just the year' ,' 2001 ')
,('YYYYMM' ,' 200112 ')
,('YYYY MMM' ,'2001 Dec')
,('YYYY-MMM' ,'2001-Dec')
,('YYYY . MMM' ,'2001 . Dec')
,('YYYY / MMM' ,'2001 / Dec')
,('YYYY - MMM' ,'2001 / Dec')
,('Forgot The Spaces #1' ,'2001December26')
,('Forgot The Spaces #2' ,'2001Dec26')
,('Forgot The Spaces #3' ,'26December2001')
,('Forgot The Spaces #4' ,'26Dec2001')
,('Forgot The Spaces #5' ,'26Dec2001 13:51:01')
,('Forgot The Spaces #6' ,'26Dec2001 13:51:01PM')
,('Oddly, this doesn''t work' ,'2001-12')
,('Oddly, this doesn''t work' ,'12-2001')
) v (Description,StringDT)
;
Итак, да... SQL Server действительно имеет довольно гибкий метод обработки всевозможных странных временных форматов, и никакой специальной обработки не требуется. Нам даже не нужно было удалять "PM", которые были добавлены к 24-часовому времени. Это "PFM" (чистая магия).
Все будет немного отличаться в зависимости от ЯЗЫКА, который вы выбрали для своего сервера, но многие из них будут обработаны в любом случае.
И эти "автомагические" преобразования не являются чем-то новым. Они прошли очень долгий путь назад.
Октябрь 2021 г.
Больше всего голосов получили ответы от guravg и Taptronic . Однако есть один вклад, который я хотел бы сделать.
Конкретный номер формата, который они показали от 0 до 131, может варьироваться в зависимости от вашего варианта использования (см. Полный список номеров здесь ), формат ввода может быть недетерминированным в зависимости от числа , что означает, что ожидаемый результат не соответствует одному случаю к другому.
Начиная с SQL Server 2005 и его уровня совместимости 90, неявное преобразование даты стало недетерминированным. Преобразование дат стало зависеть от SET LANGUAGE и SET DATEFORMAT, начиная с уровня 90.
Недетерминированные значения
0-100,
106,
107,
109,,
130. Я пытаюсь объяснить себя следующим примером:
--/ Using language 'en', returns: May 10th of 2021..
Convert(varchar(30),'05 10 2021 17:01:33:777', 113)
--/ Using language 'es', returns: October 5th of 2021...
Convert(varchar(30),'05 10 2021 17:01:33:777', 113)
Как видите, значение
113 (
dd mon yyyy hh:mi:ss:mmm) результат различается в зависимости от
LANGUAGE/DATEFORMATконфигурация. Подробности здесь .
Наилучший вариант - придерживаться детерминированных настроек, сейчас я предпочитаю
ISO форматы (
12,
112,
23,
126), поскольку они кажутся наиболее стандартными для ИТ-специалистов.
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)
Лично, если вы имеете дело с произвольными или полностью нестандартными форматами, при условии, что вы знаете, что они опередили или будут делать это, просто используйте regexp, чтобы получить нужные вам части даты и сформировать действительный компонент date/datetime.
На этой странице есть некоторые ссылки для всех указанных преобразований даты и времени, доступных для функции CONVERT. Если ваши значения не попадают в один из приемлемых шаблонов, то я думаю, что лучше всего пойти по пути ParseExact.
Если вы хотите, чтобы SQL Server попытался это выяснить, просто используйте CAST CAST("как угодно", как дата и время). Однако в целом это плохая идея. Есть проблемы с международными датами, которые могут возникнуть. Итак, как вы обнаружили, чтобы избежать этих проблем, вы хотите использовать ODBC канонический формат даты. Это формат № 120, 20 - формат, состоящий всего из двух цифр года. Я не думаю, что SQL Server имеет встроенную функцию, которая позволяет вам предоставлять пользовательский формат. Вы можете написать свое собственное и даже найти его, если будете искать в Интернете.
Неявно конвертировать строку в datetime в 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
Я нашел эту статью полезной при создании моей функции, которая преобразует строковые значения из .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
Вы можете легко добиться этого, используя этот код.
ВЫБРАТЬ Convert(datetime, Convert(varchar(30),'10/15/2008 10:06:32 PM',102),102)
Этот код решает мою проблему:
convert(date,YOUR_DATE,104)
Если вы используете
timestamp вы можете использовать приведенный ниже код:
convert(datetime,YOUR_DATE,104)