Я хочу преобразовать такую строку:
'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')
Этот вопрос подразумевает, что я должен проанализировать строку в одном из стандартные форматы, а затем преобразовать с использованием одного из этих кодов. Это кажется нелепым для такой обыденной операции. Есть способ попроще?
SQL Server (2005, 2000, 7.0) не имеет гибкого или даже негибкого способа взять произвольно структурированное datetime в строковом формате и преобразовать его в тип данных datetime.
Под «произвольно» я имею в виду «форму, которую человек, который ее написал, хотя, возможно, не вы, я или кто-то на другом конце планеты, счел бы интуитивно понятной и совершенно очевидной». Честно говоря, я не уверен, что существует такой алгоритм.
9/6/12, 6 сентября 2012 г., 9 июня 2012 г., 6 декабря 2009 г. или что-то еще?
- person Aaron Bertrand; 24.12.2012
Попробуй это
Cast('7/7/2011' as datetime)
а также
Convert(varchar(30),'7/7/2011',102)
См. CAST and CONVERT (Transact-SQL) подробнее.
Cast('2011-07-07' as datetime) также работает и устраняет двусмысленность в отношении порядка месяца и дня.
- person Joe DeRose; 06.04.2015
SET DATEFORMAT dmy перед запросом
- person Nathan Griffiths; 27.09.2017
Пропустите это через свой обработчик запросов. Он форматирует даты и / или время, и одно из них должно дать вам то, что вы ищете. Адаптироваться несложно:
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);
Я настоятельно рекомендую вам лучше контролировать и дезинфицировать свои данные о датах. Дни, когда людям разрешалось вводить даты в любом формате, который они хотят, в поле формы с произвольным текстом, должны быть уже далеко позади. Если кто-то войдет в 8/9/2011, это будет 9 августа или 8 сентября? Если вы заставите их выбрать дату в элементе управления календарем, приложение сможет управлять форматом. Как бы вы ни пытались предсказать поведение своих пользователей, они всегда найдут более глупый способ ввести дату, на которую вы не планировали.
Однако до Денали я думаю, что у @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 - 9 июня или 6 сентября.
Использовать этот:
SELECT convert(datetime, '2018-10-25 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
И обратитесь к таблице в официальной документации для кодов преобразования.
Лучшее решение этой проблемы, которое я использую, - иметь в Sql Server 2005 функцию CLR, которая использует одну из функций DateTime.Parse или ParseExact для возврата значения DateTime в указанном формате.
Краткий ответ:
SELECT convert(date, '10/15/2011 00:00:00', 101) as [MM/dd/YYYY]
Другие форматы даты можно найти на странице Помощник по SQL Server ›Форматы даты SQL Server < / а>
select convert(date,'10/15/2011 00:00:00',101). Подробнее о формате и почему 101 см. На docs.microsoft.com/en-us/sql/t-sql/functions/
- person anotherUser; 05.03.2018
У меня ушла минута, чтобы понять это, так что вот оно, на случай, если это может кому-то помочь:
В 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" (Чистая Волшебная Магия).
Все будет немного отличаться в зависимости от ЯЗЫКА, который вы выбрали для своего сервера, но большая часть из них будет обработана в любом случае.
И эти «автомагические» преобразования не являются чем-то новым. Они прошли очень долгий путь назад.
На этой странице есть ссылки на все указанные преобразования даты и времени, доступные для функции CONVERT. Если ваши значения не попадают в один из приемлемых шаблонов, я думаю, что лучше всего пойти по маршруту ParseExact.
Лично, если вы имеете дело с произвольными или полностью нестандартными форматами, при условии, что вы знаете, какие они опережают время или будут, просто используйте регулярное выражение, чтобы вытащить разделы даты, которые вы хотите, и сформировать действительный компонент date / datetime.
Если вы хотите, чтобы SQL Server попытался выяснить это, просто используйте CAST CAST ('any' AS datetime). Однако в целом это плохая идея. Есть проблемы с международными датами, которые могут возникнуть. Итак, как вы обнаружили, чтобы избежать этих проблем, вы хотите использовать канонический формат даты 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
Вы можете легко добиться этого, используя этот код.
ВЫБРАТЬ Convert (datetime, Convert (varchar (30), '15.10.2008 10:06:32 PM', 102), 102)
Этот код решает мою проблему:
convert(date,YOUR_DATE,104)
Если вы используете timestamp, вы можете использовать следующий код:
convert(datetime,YOUR_DATE,104)
