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]
MS Query - STANDARD DATE to YEAR-MONTH and WEEK 
