MS Query - STANDARD DATE to YEAR-MONTH

Hi,
I´m no superuser when it comes to MS Access. So I use MS Query to pull
out info from an SQL database to Excel.

I have a tabel containing different "titles" or "colums".
One contains a date. When executing the query. And getting the data to
Excel the date is in the standard date format (YYYY-MM-DD).

I would like to get this data in a different format directly from/in
the query.
I need it in the following format: YYYY-MM

I am using a standard "Select command".
SELECT INVENTTRANS.DATEFINANCIAL
Inventtrans = the table
Datefinancial = titel/column

Hopefully someone can help me in this.

/Dani

re: MS Query - STANDARD DATE to YEAR-MONTH

Dani,

You can probably use funtions in your ms sql query... (click the sql button
if necessary)

select year(INVENTTRANS.DATEFINANCIAL) & '-' &
month(INVENTTRANS.DATEFINANCIAL) as dfDate from INVENTTRANS

That would return a string/character type so you might need to modify the
format of the column on the sheet for sorting and stuff.

Good luck!




"Dani" <dani@papegoja.net> wrote in message
news:78de0d3c.0310140516.53032f46@posting.google.c om...[color=blue]
> Hi,
> I´m no superuser when it comes to MS Access. So I use MS Query to pull
> out info from an SQL database to Excel.
>
> I have a tabel containing different "titles" or "colums".
> One contains a date. When executing the query. And getting the data to
> Excel the date is in the standard date format (YYYY-MM-DD).
>
> I would like to get this data in a different format directly from/in
> the query.
> I need it in the following format: YYYY-MM
>
> I am using a standard "Select command".
> SELECT INVENTTRANS.DATEFINANCIAL
> Inventtrans = the table
> Datefinancial = titel/column
>
> Hopefully someone can help me in this.
>
> /Dani[/color]

re: MS Query - STANDARD DATE to YEAR-MONTH

Umm... you may have trouble with that on second thought....

Try this instead...

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


"Jerry Boone" <jerryREMOVETHIS@gomaps.com> wrote in message
news:K8Uib.66$PW5.1@newssvr24.news.prodigy.com...[color=blue]
> Dani,
>
> You can probably use funtions in your ms sql query... (click the sql[/color]
button[color=blue]
> if necessary)
>
> select year(INVENTTRANS.DATEFINANCIAL) & '-' &
> month(INVENTTRANS.DATEFINANCIAL) as dfDate from INVENTTRANS
>
> That would return a string/character type so you might need to modify the
> format of the column on the sheet for sorting and stuff.
>
> Good luck!
>
>
>
>
> "Dani" <dani@papegoja.net> wrote in message
> news:78de0d3c.0310140516.53032f46@posting.google.c om...[color=green]
> > Hi,
> > I´m no superuser when it comes to MS Access. So I use MS Query to pull
> > out info from an SQL database to Excel.
> >
> > I have a tabel containing different "titles" or "colums".
> > One contains a date. When executing the query. And getting the data to
> > Excel the date is in the standard date format (YYYY-MM-DD).
> >
> > I would like to get this data in a different format directly from/in
> > the query.
> > I need it in the following format: YYYY-MM
> >
> > I am using a standard "Select command".
> > SELECT INVENTTRANS.DATEFINANCIAL
> > Inventtrans = the table
> > Datefinancial = titel/column
> >
> > Hopefully someone can help me in this.
> >
> > /Dani[/color]
>
>[/color]

re: MS Query - STANDARD DATE to YEAR-MONTH

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

Maybe you could help perfect the 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
/Dani

*** 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

This should work for that....

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

Glad I could help, thanks for the nice comments.

:)

The thing to remember is that you are sending a query to sql server. It
doesn't matter so much as what application is working with sql (msquery,
access, et..). The thing you need is a good sql server query reference. If
you have access to the sql server cdrom you can install the "Books OnLine"
and you would be surprised at how much information is in there. You will
mainly want to search the T-Sql section since this type of code is Transact
Sql.

Good luck!




"Papegoja" <anonymous@devdex.com> wrote in message
news:3f94ff4c$0$194$75868355@news.frii.net...[color=blue]
> Hi Jerry!
> This workt great!!! You rock! Thanks a million! If you lived in Sweden I
> would have sent you a bottle of wine!
>
> Maybe you could help perfect the 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
> /Dani
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

re: MS Query - STANDARD DATE to YEAR-MONTH

Oh yeah...

And if you want it to print the name "Week" in front do this...

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


"Jerry Boone" <jerry@gomaps.com.nospam> wrote in message
news:_mRlb.939$B57.341@newssvr23.news.prodigy.com. ..[color=blue]
> This should work for that....
>
> SELECT datename(wk, INVENTTRANS.DATEFINANCIAL)) AS 'dfWeek'
> FROM INVENTTRANS
>
> Glad I could help, thanks for the nice comments.
>
> :)
>
> The thing to remember is that you are sending a query to sql server. It
> doesn't matter so much as what application is working with sql (msquery,
> access, et..). The thing you need is a good sql server query reference.[/color]
If[color=blue]
> you have access to the sql server cdrom you can install the "Books OnLine"
> and you would be surprised at how much information is in there. You will
> mainly want to search the T-Sql section since this type of code is[/color]
Transact[color=blue]
> Sql.
>
> Good luck!
>
>
>
>
> "Papegoja" <anonymous@devdex.com> wrote in message
> news:3f94ff4c$0$194$75868355@news.frii.net...[color=green]
> > Hi Jerry!
> > This workt great!!! You rock! Thanks a million! If you lived in Sweden I
> > would have sent you a bottle of wine!
> >
> > Maybe you could help perfect the 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
> > /Dani
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it![/color]
>
>[/color]