May 7, 2015 at 8:27 am
Hi,
I have column called DateDesc and the values are "Start JAN","END MAY", "Mid JUNE". Like this i have for all the months of the year.
I needed to convert this to actual date based on START/Mid/END
for example :
Start JAN : 2015/01/01
END MAY : 2015/05/31
Mid JUNE : 2015/06/15
Conceptually,
Start : start date of month
Mid: 15 of of the month
End : End date of the month
Any sample query how to achieve this please
May 7, 2015 at 8:52 am
KGJ-Dev (5/7/2015)
Hi,I have column called DateDesc and the values are "Start JAN","END MAY", "Mid JUNE". Like this i have for all the months of the year.
I needed to convert this to actual date based on START/Mid/END
for example :
Start JAN : 2015/01/01
END MAY : 2015/05/31
Mid JUNE : 2015/06/15
Conceptually,
Start : start date of month
Mid: 15 of of the month
End : End date of the month
Any sample query how to achieve this please
That is ugly!!! The months are not even consistent. Jan is the 3 character abbreviation but June is the entire month. YUCK!!!! Parsing the month name may prove to be extremely difficult if this is a freeform field. Once you have the month it is painless to get the start, end and 15th.
http://qa.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2015 at 8:59 am
Hey sean,
Thanks for your reply, i am sorry that i misspelled. it is 3 char month. JAN/FEB/MAR/APR/MAY/JUN...
Any thought/ sample pelase
May 7, 2015 at 9:09 am
KGJ-Dev (5/7/2015)
Hey sean,Thanks for your reply, i am sorry that i misspelled. it is 3 char month. JAN/FEB/MAR/APR/MAY/JUN...
Any thought/ sample pelase
What have you tried?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2015 at 9:23 am
I have created months table. trying with charindex but no idea how to move forward. would you like to help me in this please
May 7, 2015 at 10:29 am
I came up with this as a simple example of what you are trying to accomplish:
with Months as (select MonthAbbr, MonthNumber from (values ('JAN',1),('FEB',2),('MAR',3),('APR',4),('MAY',5),('JUN',6),('JUL',7),('AUG',8),('SEP',9),('OCT',10),('NOV',11),('DEC',12))dt(MonthAbbr, MonthNumber))
, TestData as (select TestDescription from (values ('START JAN'),('END MAY'),('MID JUN'))dt(TestDescription))
select
*,
case when UPPER(left(TestDescription,charindex(' ',TestDescription) - 1)) = 'START' then dateadd(month, MonthNumber - 1, dateadd(year,year(getdate()) - 1900,0))
when UPPER(left(TestDescription,charindex(' ',TestDescription) - 1)) = 'END' then dateadd(day,-1,dateadd(month, MonthNumber, dateadd(year,year(getdate()) - 1900,0)))
when UPPER(left(TestDescription,charindex(' ',TestDescription) - 1)) = 'MID' then dateadd(day, 14,dateadd(month, MonthNumber - 1, dateadd(year,year(getdate()) - 1900,0)))
end
from
TestData td
inner join Months m
on (td.TestDescription like '%' + m.MonthAbbr + '%');
Questions?
May 7, 2015 at 10:33 am
I was about to post my version but Lynn beat me to it. On second thought, I will post my version anyway. It is a very different approach from his but produces the same results.
with Something as
(
select 'Start JAN' as SomeValue union all
select 'END MAY' union all
select 'Mid JUN'
)
, DateStrings as
(
select
case LEFT(SomeValue, charindex(' ', SomeValue, 0))
when 'Start' then '2015-' + RIGHT(SomeValue, 3) + '-01'
when 'Mid' then '2015-' + RIGHT(SomeValue, 3) + '-15'
when 'END' then '2015-' + RIGHT(SomeValue, 3) + '-01'
end as MyDate
, LEFT(SomeValue, charindex(' ', SomeValue, 0)) as MonthPart
from Something
)
select
case MonthPart
when 'Start' then CAST(MyDate as datetime)
when 'Mid' then CAST(MyDate as datetime)
when 'END' then DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(MyDate as datetime)))
end
, MonthPart
from DateStrings;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2015 at 11:37 am
Thanks you lynn and Sean, much appreciated.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply