Date Part Year and Month (1 Viewer)

S

sross81

Registered User.

Local time

Today, 08:24

Joined

Oct 22, 2008

Messages

97

• Sep 4, 2012

• #1

Is there a way to extract the year and month only from a date field and create a new field?

I have tried DatePart("yyyymm", [DischDate]) and multiple little versions of that and I can't seem to get anything to work.

I tried to do the year and month separately and then concatentate them together and then it drops off the leading 0.

I want the final result to be for example 201207

Thank you.

 

M

Mr. B

"Doctor Access"

Local time

Today, 10:24

Joined

May 20, 2009

Messages

1,932

• Sep 4, 2012

• #2

Add the following to a new column in the QBE:

YrMo: Year([OrderDate]) & Format(Month([OrderDate]),"mm")

This will great a new column called "YrMo" with the format of "YYYYMM".

 

S

sross81

Registered User.

Local time

Today, 08:24

Joined

Oct 22, 2008

Messages

97

• Sep 4, 2012

• #3

Here is my field:

YrMo: Year([DischargeDateTime]) & Format(Month([DischargeDateTime]),"mm")


The weird thing is that in the DischargeDateTime field I have dates like 7/12/2012 15:20 etc, but the YrMo retuns 201201.

Is this because of the month not having the leading 0? Is there any way around that?

 

spikepl

Eledittingent Beliped

Local time

Today, 17:24

Joined

Nov 3, 2010

Messages

6,142

• Sep 4, 2012

• #4

YrMo: Format([DischargeDateTime], "yyyymm")

 

S

sross81

Registered User.

Local time

Today, 08:24

Joined

Oct 22, 2008

Messages

97

• Sep 4, 2012

• #5

Perfect.

 

V

varunmathur

Registered User.

Local time

Today, 16:24

Joined

Feb 3, 2001

Messages

68

• Sep 24, 2013

• #6

The problem is that when you sort in ascending order:
April comes before Jan or Feb or mar

 

N

namliam

The Mailman - AWF VIP

Local time

Today, 17:24

Joined

Aug 11, 2003

Messages

11,695

• Sep 24, 2013

• #7

No, Varunmathur, not with a YYYYMM format, not in text, not in number.... Not even if you do MMYYYY but you will get all januaris then all febs if you include different years.

Your problem only is true if you do MMM instead.

FYI, if for whatever reason you prefer numberic YYYYMM try:
Year(DischDate) * 100 + Month(DischDate)
or
int(Format([DischDate], "yyyymm") )

 

V

varunmathur

Registered User.

Local time

Today, 16:24

Joined

Feb 3, 2001

Messages

68

• Sep 24, 2013

• #8

Thanks Namliam
yes you are right I tried to get the month name it did'nt work so I also tried
DatePartM: (DatePart("m",[VisitDate])) & "/ " & DatePart("yyyy",[VisitDate])
it works fine