MS Query - STANDARD DATE to YEAR-MONTH and WEEK

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 don´t want to have to do this in Excell. I´m 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!

re: MS Query - STANDARD DATE to YEAR-MONTH and WEEK

"Papegoja" <anonymous@devdex.com> wrote in message
news:3f94fa92$0$197$75868355@news.frii.net...[color=blue]
> 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 don´t want to have to do this in Excell. I´m pulling the date directly
> into a pivottable. And would like the whole report to be self updatable.
>
> Is this possible?
>
> Best regards
> /Papegoja[/color]


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

re: MS Query - STANDARD DATE to YEAR-MONTH and WEEK

SELECT 'Week ' + datename(wk, INVENTTRANS.DATEFINANCIAL)) AS 'dfWeek'
FROM INVENTTRANS


"Fletcher Arnold" <fletch@home.com> wrote in message
news:bn2uv8$ers$1@sparta.btinternet.com...[color=blue]
> "Papegoja" <anonymous@devdex.com> wrote in message
> news:3f94fa92$0$197$75868355@news.frii.net...[color=green]
> > 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 don´t want to have to do this in Excell. I´m pulling the date directly
> > into a pivottable. And would like the whole report to be self updatable.
> >
> > Is this possible?
> >
> > Best regards
> > /Papegoja[/color]
>
>
> 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
>
>[/color]