By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,436 Members | 2,958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,436 IT Pros & Developers. It's quick & easy.

convert month number to month name

P: 5
I saw previous solutions to convert a month number (1) to a month name (January). I have a database with 200+ dates. In a query, I use the "Part" function DatePart("m",[full_date]), to extract the month (number) and all month extracts worked correctly (they returned the correct numerical month). However, when I attempted adding a field in the query to format that extracted month to a month name, it only returned January and December - for all 200+ records. What did I do wrong and how can I correctly get the month name to appear.

thanks
Sep 16 '09 #1

✓ answered by ajalwaysus

Without a good idea of your code structure I would say simply replace your
Expand|Select|Wrap|Line Numbers
  1. DatePart("m",[full_date])
with
Expand|Select|Wrap|Line Numbers
  1. Format([full_date],"mmmm")
wherever you have it in your code.

-AJ

Share this Question
Share on Google+
12 Replies


Expert 100+
P: 266
Be Borth,

You need to show us what you did, not just tell us, so that we can review it and offer up a possible solution.

-AJ
Sep 16 '09 #2

P: 5
Thanks AJ - I'm happy to do that, but the database contains a lot of personal information (names, birthdates, etc) that I would be uncomfortable posting on a public website. Any suggestions?
ben
Sep 16 '09 #3

Expert 100+
P: 266
All I need to see is the function you are using to convert the date. Perhaps your DatePart function along with whatever you are using to convert the DatePart to a date name. Just copied and pasted into the thread, also note, please use the [code] brackets when posting code.

-AJ
Sep 16 '09 #4

missinglinq
Expert 2.5K+
P: 3,532
Format([full_date],"mmmm") should do it for you.

Welcome to Bytes!

Linq ;0)>
Sep 16 '09 #5

P: 5
Ok, AJ, thanks. The datafile has a field "full_date" which might be 1/8/1953 for example. In the query, I used the DatePart function to extract the month number in the following way [event_date_month: DatePart("m",[full_date])]. This successfully returns the correct numerical value for the months. However, when I try to create a new field in the query to convert that value to a month name using the Format function I only get "December" or "January". The same thing happens if I try to change the format of "event_date_month" in the query or the report using the properties drop-down menu
Sep 16 '09 #6

Expert 100+
P: 266
Let me know if missinglinq's code works for what you are doing.

-AJ
Sep 16 '09 #7

P: 5
thanks Linq - I've seen that response before and I'm not sure where to put that code. Do I create a new field or where exactly do I embed the code Format([full_date],"mmmm") in the query? Sorry for being kinda slow on the uptake
Sep 16 '09 #8

Expert 100+
P: 266
Without a good idea of your code structure I would say simply replace your
Expand|Select|Wrap|Line Numbers
  1. DatePart("m",[full_date])
with
Expand|Select|Wrap|Line Numbers
  1. Format([full_date],"mmmm")
wherever you have it in your code.

-AJ
Sep 16 '09 #9

P: 5
It works! Thanks AJ, and thanks Linq!!
Sep 16 '09 #10

ahmedtharwat19
P: 55
we can use this code
Expand|Select|Wrap|Line Numbers
  1. monthname(datepart("m",[full_date]))
  2.  
Oct 21 '14 #11

twinnyfo
Expert Mod 2.5K+
P: 3,284
ahmedtharwat19,

First, this is a five year old thread, so the OP probably has lost oversight of this question long ago.

Second, ajalwaysus' answer in Post #9, not only was selected as a correct answer, but is actually the "best" answer, as your solution causes the system to utilize two functions rather than just one. one iteration may only take a few milliseconds longer, but if there were millions of dates to transpose, your solution would take approximately twice as long.
Oct 21 '14 #12

P: 1
@ahmedtharwat19
Ahmed,

Thank you very much
Feb 3 '19 #13

Post your reply

Sign in to post your reply or Sign up for a free account.