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://www.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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy