Excel/MS Query: Change format of date and dollar amount

[althea]

I have an excel spreadsheet and am getting data from my SQL database. In MS Query I have a query to get 3 fields from a database but need it to look better.

When I try and use the 'format' next to date field, it gives me an error about 'does not recognize format command'.

Do I add the format(tablename.fieldname,"mm/dd/yyyy") in the 'select', 'where' and 'order by' areas of the statement?Currently I have it only in the select part of the statement.Thanks!

 

[SkipVought]

Hi,

Please post the SQL that you are using.

Please post an example of the SQL that you tried, getting the posted error.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[althea]

Here is what I have currently:

SELECT DailyDeposit.PrimarySiteId, format(DailyDeposit.ClosingDate,"mm-dd-yyyy"), DailyDeposit.DepositAmount
FROM SupportDB.dbo.DailyDeposit DailyDeposit
WHERE (format(DailyDeposit.ClosingDate,"mm-dd-yyyy") >=? And format(DailyDeposit.ClosingDate,"mm-dd-yyyy")<=?)
ORDER BY DailyDeposit.PrimarySiteId, DailyDeposit.ClosingDate

Maybe I shouldn't have the format so many times?

error 'format is not a recognized built-in fuction name. statement could not be prepared'.

 

[SkipVought]

faq183-6628

SQL Server has an entirely different syntax than MS Access or SM Query. I am not very familiar with SQL Server syntax, so I have posted this FAQ.

Also forum183

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[althea]

This is what I have entered using the 'sql' button in MS Query (which I get to from Excel) for the query to get data for my spreadsheet.

 

[SkipVought]

and is it working?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

for instance, I query an Oracle DB in MS Query. I can drag a DATE into a my query grid. But if I want to convert the date to a string, instead of

Format(MyDate, 'mm/dd/yyyy'), it would be,
To_Char(MyDate, 'mm/dd/yyyy')



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[althea]

When I use 'to_char' I get the error 'to_char is not a recognized built-in function name. statement could not be prepared.'

Maybe it's because it's not an oracle db, it's a sql db? Thanks!

 

[SkipVought]

Did you read & understand the FAQ I posted???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhulbert]

First question are the "Dates" on the SQL server actualy held as date fields if so they should work fine in your excel query if not go back to your SQL guys about getting a view with proper dates in not string data.

Personally I would drag the data out then use format cells in Excel to improve the look of your query.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[althea]

I'm still working on this. Thanks again for all of your help ahead of time.

Yes I read your link on 'FAQ a complete guide to dates'. I did not see anywhere on the link about formatting dates coming from 'ms query'.

Someone else suggested I 'drag the data out then use format cells'. How could I do this if the data changes? The columns are always the same but the rows could be from 1 to 250 depending on the date range needed.

What I would really like to do is see the date formatted as 'mm/dd/yyyy' instead of how it's coming out of the sql database as 'yyyymmdd'.

Thanks!

 

[SkipVought]

I did not see anywhere on the link about formatting dates coming from 'ms query'.

The link has NOTHING to do with MS Query.

It has EVERYTHING to do with SQL Server.

As I previously stated, when I query Oracle, using MS Query, I CANNOT USE MS QUERY SYNTAX!!!

Rather, I use ORACLE SYNTAX.

Similarly, YOU WILL NEED TO USE SQL SERVER SYNTAX!

Go figure it out, using the posted reference!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

format(DailyDeposit.ClosingDate,"mm-dd-yyyy")

should be something like

convert(date,DailyDeposit.ClosingDate,102)

or similar

or you could just format the cells in excel...depending on the version of excel the formatting should move down with the data. If it does not, just format the whole column...not best practice but not going to kill the spreadsheet...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[althea]

Thank you so much! It works great now using the 'convert' function. One more question, how can I format the dollar amount field so that instead of 322 I would see $322.00.

Thanks!

 

[xlbo]

you could use CAST or CONVERT for that. Bear in mind that by doing this you are making your data pretty useless for any further aggregation as the addition of the $ means that what you are pulling back is text rather than a number - the $ really should just be there as part of a format but that's up to you...

'$' + cast(cast(DailyDeposit.DepositAmount as money) as varchar(50))

OR

'$' + convert(varchar(50),convert(money, DailyDeposit.DepositAmount))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[althea]

Thank you! Yes the convert worked great.

One more issue:
I'm using parameters based on two cell addresses so that the data displayed in the query is a range from a beginning date until ending date. Currently the two cells where the user enters a date are 'number' format. I'm trying to make this easier for the user to enter dates. Instead of '20110216' I'd like them to have the option to enter the date as 2-16-2011. When I change the format of the cell to date the query stops working. any ideas?

 

[SkipVought]

Currently the two cells where the user enters a date are 'number' format. I'm trying to make this easier for the user to enter dates. Instead of '20110216' I'd like them to have the option to enter the date as 2-16-2011. When I change the format of the cell to date the query stops working. any ideas?

faq68-5827.

'20110216' is not a DATE, as you can now understand, having read faq68-5827.

However, you can use Data > Text to columns and select the YMD Date conversion option in the Wizard to convert all your values to REAL DATES.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[althea]

Thanks for your suggestion. The article is very helpful. I think I wasn't clear on what happens. Once I change the format from number to 'date' on the cell, it stops working. My format of both cells is now 'date' and the text entered in each cell has format of 2/16/2011. This is because I chose the type '3/14/2001'.

Do you know if there is a rule where it can only read a parameter cell of a certain format?

 

[SkipVought]

Your dates are actually NUMBERS: 2/16/2011 is really 40590, as you now know.

Please post your QUERY SQL, showing how you reference your date parameters.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[althea]

Thanks. Here is the code I have:

SELECT DailyDeposit.PrimarySiteId, convert(date,DailyDeposit.ClosingDate,110) AS 'Deposit Date', '$'+convert(varchar,DailyDeposit.DepositAmount,1) AS 'Deposit Amount'
FROM SupportDB.dbo.DailyDeposit DailyDeposit
WHERE (DailyDeposit.ClosingDate>=? And DailyDeposit.ClosingDate<=?)
ORDER BY DailyDeposit.PrimarySiteId, DailyDeposit.ClosingDate

From Excel, when going to parameters, I changed it to 'get the value from the following cell'. It does not recognize that in the sql statement.