• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

SELECT MONTH FROM A GIVEN DATE

Hi,

I am trying to get the month part from a date which I get by using a max fuction.
My date comes from a select statement which goes like this:

select max(Invoice Date)

This returns me  the latest date .(Ex:8/31/2004)

From this date I need to get the month number.

So my query should return 8.

Do I need a sub query for this.Please help as I am a novice to SQL Server.

Thanks,
MARK
0
newcom408
Asked:
newcom408
  • 7
  • 4
  • 3
  • +1
1 Solution
 
jdlambert1Commented:
SELECT Month(Max(InvoiceDate))
0
 
newcom408Author Commented:
Hi jdlambert1,

Do i get this value as a number or as a char.
Please let me know what the query would look like for

1)Number format
2)Char Format

Thanks,
MARK
0
 
Anthony PerkinsCommented:
1) See previous solution.
2) SELECT MonthName(max([Invoice date])
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Anthony PerkinsCommented:
Ignore my last comment.
0
 
Anthony PerkinsCommented:
It should have been:
1) See previous solution.
2) SELECT DATENAME(month, max([Invoice date])
0
 
newcom408Author Commented:
Hi guys,
That helps a lot.
Sorry I have one more how would I get the same values for the previous month.
I mean if I have a date like 8/31/2004 and if I need to get the previous month number from this how would I do that.

Thanks,
MARK.
0
 
newcom408Author Commented:
Hi guys,

I am increasing the point value as I think I might have many questions based on these date functions.

Thanks,
MARK
0
 
ram2098Commented:
1. SELECT month([invoice date])-1

2. select datename(month,dateadd(mm,-1,[invoice date]))

'-1' in both the queries ..you can change based on the month you want to display. (This is specific to previous month
0
 
Anthony PerkinsCommented:
SELECT MONTH(DATEADD(month, -1, max(Invoice Date)))

Or:
SELECT DATENAME(month, DATEADD(month, -1, max(Invoice Date)))

You may want to read up on the date time functions in BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2c1f.asp
0
 
ram2098Commented:
Ofcourse, even if you want to make it dynamic, you can pass the value of '-1' as a paramter too..like '0' for current month, '-1' for previous month etc..
0
 
Anthony PerkinsCommented:
ram2098,

>>SELECT month([invoice date])-1<<
Just a word of caution: This could be a tad problematic in January
0
 
ram2098Commented:
acperkins..you are right!! thanks.
0
 
newcom408Author Commented:
Hi guys,

I understand that this statement gives me the month name
SELECT MonthName(max([Invoice date])

but what if i want the month numbe rin char format

Thanks,
MARK.
0
 
Anthony PerkinsCommented:
>>I understand that this statement gives me the month name
SELECT MonthName(max([Invoice date])<<
No it does not.  That was my mistake, which I retracted after the fact.  You really need to check out BOL and do some testing in SQL Query Analyzer.

>>but what if i want the month numbe rin char format<<
SELECT CAST(MONTH(max(Invoice Date)) As char(2))
0
 
Anthony PerkinsCommented:
Also, please follow-up on this abandoned question:
http://www.experts-exchange.com/Databases/Oracle/Q_21023541.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now