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

MS Query - STANDARD DATE to YEAR-MONTH

P: n/a
Hi,
Im 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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" <da**@papegoja.net> wrote in message
news:78**************************@posting.google.c om...
Hi,
Im 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

Nov 12 '05 #2

P: n/a
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" <je*************@gomaps.com> wrote in message
news:K8************@newssvr24.news.prodigy.com...
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" <da**@papegoja.net> wrote in message
news:78**************************@posting.google.c om...
Hi,
Im 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


Nov 12 '05 #3

P: n/a
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 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
/Dani

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

P: n/a
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" <an*******@devdex.com> wrote in message
news:3f*********************@news.frii.net...
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 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
/Dani

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #5

P: n/a
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" <je***@gomaps.com.nospam> wrote in message
news:_m***************@newssvr23.news.prodigy.com. ..
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" <an*******@devdex.com> wrote in message
news:3f*********************@news.frii.net...
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 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
/Dani

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.