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

MS Query - STANDARD DATE to YEAR-MONTH and WEEK

P: n/a
Hi,
A week ago I had a question how to get MS Query to return a standard
date to a "YEAR-MONTH" (YYYY-MM) format.

I received a great answer from Jerry Boone!!!

It follows:
SELECT convert(varchar(4),year(INVENTTRANS.DATEFINANCIAL) ) + '-' +
convert(varchar(2),month(INVENTTRANS.DATEFINANCIAL )) AS 'dfDate'

This workt great!!! Jerry, you rock! Thanks a million! If you lived in
Sweden I would have sent you a bottle of wine!

Maybe someone els, of Jerry, could help me to percect my report. I would
like to get MS Query to return the same date in "weeks". 2003-01-01
would be week 1.

I dont want to have to do this in Excell. Im pulling the date directly
into a pivottable. And would like the whole report to be self updatable.

Is this possible?

Best regards
/Papegoja

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Papegoja" <an*******@devdex.com> wrote in message
news:3f*********************@news.frii.net...
Hi,
A week ago I had a question how to get MS Query to return a standard
date to a "YEAR-MONTH" (YYYY-MM) format.

I received a great answer from Jerry Boone!!!

It follows:
SELECT convert(varchar(4),year(INVENTTRANS.DATEFINANCIAL) ) + '-' +
convert(varchar(2),month(INVENTTRANS.DATEFINANCIAL )) AS 'dfDate'

This workt great!!! Jerry, you rock! Thanks a million! If you lived in
Sweden I would have sent you a bottle of wine!

Maybe someone els, of Jerry, could help me to percect my report. I would
like to get MS Query to return the same date in "weeks". 2003-01-01
would be week 1.

I dont want to have to do this in Excell. Im pulling the date directly
into a pivottable. And would like the whole report to be self updatable.

Is this possible?

Best regards
/Papegoja

We had a question on week numbers recently. You need to make sure what
standard you are working to. If you are working to the ISO 8601 standard,
then Week No 1 contains the fourth day of January - this means:
Jan 1, 2003 is in week 1 of 2003
Jan 1, 2005 is in week 53 of 2004

Or do you have another standard?

Fletcher
Nov 12 '05 #2

P: n/a
SELECT 'Week ' + datename(wk, INVENTTRANS.DATEFINANCIAL)) AS 'dfWeek'
FROM INVENTTRANS
"Fletcher Arnold" <fl****@home.com> wrote in message
news:bn**********@sparta.btinternet.com...
"Papegoja" <an*******@devdex.com> wrote in message
news:3f*********************@news.frii.net...
Hi,
A week ago I had a question how to get MS Query to return a standard
date to a "YEAR-MONTH" (YYYY-MM) format.

I received a great answer from Jerry Boone!!!

It follows:
SELECT convert(varchar(4),year(INVENTTRANS.DATEFINANCIAL) ) + '-' +
convert(varchar(2),month(INVENTTRANS.DATEFINANCIAL )) AS 'dfDate'

This workt great!!! Jerry, you rock! Thanks a million! If you lived in
Sweden I would have sent you a bottle of wine!

Maybe someone els, of Jerry, could help me to percect my report. I would
like to get MS Query to return the same date in "weeks". 2003-01-01
would be week 1.

I dont want to have to do this in Excell. Im pulling the date directly
into a pivottable. And would like the whole report to be self updatable.

Is this possible?

Best regards
/Papegoja

We had a question on week numbers recently. You need to make sure what
standard you are working to. If you are working to the ISO 8601 standard,
then Week No 1 contains the fourth day of January - this means:
Jan 1, 2003 is in week 1 of 2003
Jan 1, 2005 is in week 53 of 2004

Or do you have another standard?

Fletcher

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.