DatePart results in #Func!

[bigal.nz]

Hi Guys,

I am trying to use DatePart("mm", [MAS.DateStart]) in :

SELECT DatePart("mm", [MAS.StartDate]) As Month FROM MAS;

Some results are blank, which is fine, but the majority are #Func!

The DateStart column is set to data type date. I am in NZ so we use dd/mm/yyyy.

Any ideas?

Cheers

AL

 

[pbaldy]

I think you want a single "m" for the argument.

 

[bigal.nz]

Duh. That helped. Can I also return from datepart month and year?

 

[pbaldy]

"yyyy" will return the year. If you want both together, the Format() function may be a simpler option.

 

[bigal.nz]

Yeah I want both.

 

[pbaldy]

Try

Format([StartDate],"mmyyyy")

 

[bigal.nz]

That worked perfectly - thanks.

 

[arnelgp]

its only one "m" not double. if yiu want to get the name if month use format(startdate,"mmm") or format(startdate,"mmmm")

 

[pbaldy]

No problem. Just said goodbye to my daughter, on her way home to Wellington!

 

[Mark_]

The DateStart column is set to data type date. I am in NZ so we use dd/mm/yyyy.

For future note, type "DATE" does not care what format you use. It is stored internally as a number. dd/mm/yyyy, mm/dd/yyyy, or yyyymmdd are formats for a value that would be something like 40145.331999456 in the database.

Letting you know because if you try thinking of the stored data as "18/04/2017" rather than some number you can get yourself totally confused.

 

[bigal.nz]

No problem. Just said goodbye to my daughter, on her way home to Wellington!

Windy Welly lol.

I am now trying to get a bit more tricky with the query:

SELECT MAS.Pathway, Count(*) As CountPathway, format([MAS.DateStart], "mmm yyyy") As MonthYear
FROM MAS
GROUP BY MAS.PAthway, MonthYear;

However I am not getting a count per month. I am getting:

Pathway Count Month/Year
Sales 1 Dec 2017
Sales 1 Dec 2017
Develop 1 Dec 2017
Develop 1 Dec 2017
Develop 1 Dec 2017

rather than

Sales 2 Dec 2017
Develop 3 Dec 2017

etc

Help!

 

[pbaldy]

You'd have to group by the formatted column, not the actual date.

 

[bigal.nz]

Sorted - thanks again.

 

[Mark_]

Special note; for sorting I'd recommend adding a column that you are not going to show with the date formatted as YYYYMM so that you can keep it in proper order. Hate to see December before February.

 

[pbaldy]

Happy to help!