sql server 2008 - Extract Date with different format from a text field -
i trying extract date text field, date in format of m/d/yy
, mm/d/yy
, m/dd/yy
or mm/dd/yy
. query return date datetime
(ideally in format of mm/dd/yyyy
), here examples:
table name: tblnote field name: notetext
note summary: 1/2/99 monday ...
report: 02/3/06 tuesday ...
user called in: 3/11/98 wednesday...
client feedback: 04/22/15 thursday....
is there easy way accomplish this, in advance!
here table: create table [dbo].[mynote]( [notetext] varchar null )
insert mynote values ('note summary: 4/1/17 monday'), -- m/d/yy ('report: 04/1/17 tuesday'), -- mm/d/yy ('user called in: 4/01/11 wednesday'), -- m/dd/yy ('client feedback: 04/01/17 thursday'), -- mm/dd/yy ('defendant note: 7/13/11... deft. deceased... case withdrawn... ejg')
select notetext, convert(date, substring(notetext, charindex('/',notetext)-2,8), 1) dateasdate mynote
--result: notetext | dateasdate ----- | ----- note summary: 4/1/17 monday | 2017-04-01 report: 04/1/17 tuesday | 2017-04-01 user called in: 4/01/11 wednesday | 2011-04-01 client feedback: 04/01/17 thursday | 2017-04-01 defendant note: 7/13/11... deft. deceased... case withdrawn... ejg | 2011-07-13
everything works great far, however, copied record that's conversion table, looks identical 5th record scripted, however, when ran same query, getting following error.
msg 241, level 16, state 1, line 1 conversion failed when converting date and/or time character string.
this what's in table now:
note summary: 4/1/17 monday report: 04/1/17 tuesday user called in: 4/01/11 wednesday client feedback: 04/01/17 thursday defendant note: 7/13/11... deft. deceased... case withdrawn... ejg defendant note: 7/13/11... deft. deceased... case withdrawn... ejg
here copy of database table, should able restore db , duplicate issue. https://drive.google.com/file/d/0b_mjbfxs2fwfwvbbdvhks1r6rg8/view?usp=sharing
thanks!
well, lucky format pretty same, defers in number of digits month or day.
means can use convert
, since sql server know how handle this.
create , populate sample table (please save step in future questions):
declare @t table ( dateasstring varchar(8) ) insert @t values ('4/1/17'), -- m/d/yy ('04/1/17'), -- mm/d/yy ('4/01/17'), -- m/dd/yy ('04/01/17') -- mm/dd/yy
the query:
select dateasstring, convert(date, dateasstring, 1) dateasdate @t
results:
dateasstring dateasdate 4/1/17 01.04.2017 00:00:00 04/1/17 01.04.2017 00:00:00 4/01/17 01.04.2017 00:00:00 04/01/17 01.04.2017 00:00:00
Comments
Post a Comment