June 11, 2014 at 9:12 am
My table has the field called MOnth_year which is "Apr-2014" ,"Aug-2013"
For query purposes , i need to get only month numbers and year as seperate columns
Like Apr = 4
How can i get that?
June 11, 2014 at 9:15 am
sharonsql2013 (6/11/2014)
My table has the field called MOnth_year which is "Apr-2014" ,"Aug-2013"For query purposes , i need to get only month numbers and year as seperate columns
Like Apr = 4
How can i get that?
DATEPART?
You have been around long enough to know that we need a little bit of detail. At the very least we need to know the datatype of the column.
_______________________________________________________________
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/
June 11, 2014 at 9:17 am
Yeah was about to inform , its varchar .Datepart not working
June 11, 2014 at 9:20 am
select datepart(mm, cast( Month_Year as datetime)) gives conversion error too
June 11, 2014 at 9:30 am
sharonsql2013 (6/11/2014)
select datepart(mm, cast( Month_Year as datetime)) gives conversion error too
Of course it won't convert. "Apr-2014" is not a valid date. This kind of stuff would be SO much easier if you used date datatypes to hold date information.
PLEASE include ddl and sample data in your posts. It makes it a LOT easier for us volunteers to work on your issues.
create table #something
(
SomeValue varchar(10)
)
insert #something(SomeValue)
Values ('Apr-2014'), ('Aug-2013')
select
datepart(month, cast(replace(SomeValue, '-', '') as datetime)) as MyMonth
, datepart(year, cast(replace(SomeValue, '-', '') as datetime)) as MyMonth
from #something
drop table #something
_______________________________________________________________
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/
June 11, 2014 at 9:34 am
Sure will do.
Thanks a lot.Made my day! Saved me a lot of work.
June 11, 2014 at 9:37 am
sharonsql2013 (6/11/2014)
Sure will do.Thanks a lot.Made my day! Saved me a lot of work.
You're welcome. Glad that worked for you. 😀
_______________________________________________________________
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply