ExcelIntroText with formatted date

I wrote a script which exports its result set to an Excel file using the @export client-side command. I tried to incorporate the report timeframe variables into the ExcelIntroText value using: ExcelIntroText="Diagnosis date in ${Start_Date||||||format=[dd-MMM-yyyy]}$ to ${End_Date||||||format=[dd-MMM-yyyy]}$" ... but the dates still rendered with the default "yyyy-MM-dd" format. I had seen in the docs that the "format=" clause was valid for the ${dbvis-date}$ pre-defined variable and thought that it might also be valid for my variables (both the ${Start_Date}$ and ${End_Date}$ variables are defined with "type=Date"). Is this not possible?

Hi Steve, It is not possible, but it sounded like a great idea and was easy to fix so it will be possible in the next maintenance release. Best Regards, Hans

> {quote:title=Hans Bergsten wrote:}{quote} > It is not possible, but it sounded like a great idea and was easy to fix so it will be possible in the next maintenance release. Awesome. Thanks Hans!

Hi Steve, I just realized that I may have missed a detail in this, namely how your Start_Date and End_Date variables get their values. Do you get them as output from a stored procedure as true Date values, or do you assign them literal string values using @echo or similar? Please clarify this so I can make sure that we really handle your case. Best Regards, Hans

> {quote:title=Hans Bergsten wrote:}{quote} > I just realized that I may have missed a detail in this, namely how your Start_Date and End_Date variables get their values. Do you get them as output from a stored procedure as true Date values, or do you assign them literal string values using @echo or similar? > > Please clarify this so I can make sure that we really handle your case. I use @echo statements to assign the start and end date parameters at the top of the script as follows: @echo Start Date = ${Start_Date||2013-01-01|| Date || where }$; @echo End Date = ${End_Date||2013-12-31|| Date || where }$;

Hi Steve, Thanks. That will work as long as the literal value is in the format specified for Date in Tool Properties, which it is in your example (assuming you use the default format). I'm just curious about why you do not assign the value in the format you want from the beginning? Is it because you need to use the variable in a WHERE clause in a SELECT and the database only handles the yyyy-MM-dd format? Anyway, in the next maintenance release you will be able to have: @echo Start Date = ${Start_Date||2013-01-01|| Date}$; @echo End Date = ${End_Date||2013-12-31|| Date}$; SELECT ... WHERE someColumn = ${Start_Date|||| Date}$; ExcelIntroText="Diagnosis date in ${Start_Date||||Date||format=[dd-MMM-yyyy]}$ to ${End_Date||||Date||format=[dd-MMM-yyyy]}$" and get the dates formatted as dd-MMM-yyyy in ExcelIntroText. It will also be possible to use a format option for variables holding true Date/Time/Timestamp values, e.g. values returned by a function call or used for stored procedure OUT parameters. There is one thing you need to be aware of when trying this out after it is released, and that is that you cannot trust the formatting shown in the Log tab. That is because variables in the log entries are formatted based on the options specified for the last occurrence of the variable in the script. Hence, the variables in the log entries will be formatted the same regardless of what formatting option they have in the script. This is a known flaw that we have an open ticket for. Best Regards, Hans

Hi Hans, > {quote:title=Hans Bergsten wrote:}{quote} > I'm just curious about why you do not assign the value in the format you want from the beginning? Is it because you need to use the variable in a WHERE clause in a SELECT and the database only handles the yyyy-MM-dd format? I'm not sure I understand. Are you saying that when I use: @echo Start Date = ${Start_Date||2013-01-01|| Date}$; ... that the ${Start_Date}$ variable is actually a String instance, not a Date instance?

Hi Steve, @echo Start Date = ${Start_Date||2013-01-01|| Date}$; creates a variable with a literal (string) value that should be interpreted as a Date. It's the same as when you use a literal in an SQL statement, e.g. INSERT INTO aTable (aDateColumn) VALUES('2013-01-01'); The only way a DbVisualizer variable can hold a value that is not a literal is when it gets its value from a function or a stored procedure in a @call command. I hope that answers the question. I'm still curious about why you do not assign the value in the format you want from the beginning? Is it because you need to use the variable in an SQL statement and the database only handles the yyyy-MM-dd format? Best Regards, Hans

> {quote:title=Hans Bergsten wrote:}{quote} > I'm still curious about why you do not assign the value in the format you want from the beginning? Is it because you need to use the variable in an SQL statement and the database only handles the yyyy-MM-dd format? Nope. It's because I had assumed that when I used a type value of "Date" that the variable would be stored internally as a Date object rather than a String. You're correct that I could probably have just used the dd-MMM-yyyy format in my variable definition statement. So when the variable is injected into the SQL statement is it passed as a Date object or as a String object? Also, based on what the docs say it sounds like variables are passed to the DB as parameters in a java.sql.PreparedStatement. Is my understanding correct? Thanks.

Hi Hans, FYI, I tried using the dd-MMM-yyyy format in my parameter definition as follows: @echo Start Date = ${Start_Date||01-Jan-2013|| Date || where }$; ... but when I tried to run the query I got a java.lang.IllegalArgumentException (Illegal format: 01-Jan-2013) so it appears that this is not an option for me. The query is being run against a SQL*Server DB using the jTDS driver. It must be the driver that has a problem with the format because I know that SQL*Server will accept a WHERE clause like: WHERE start_date >= '01-Jan-2013'

Hi Steve, I agree that it is not obvious how it works and that my previous explanation was a bit misleading. To answer your first question, a variable with a specified type like Date is (by default) passed to the database as the specified type, using a PreparedStatement. You can change this by using the "nobind" option. If no type is specified, or if the nobind option is used for a variable with a specified type, the value is passed as a literal and it is up to the database to interpret the literal. The reason your experiment with the dd-MMM-yyyy format failed is that you must specify the literal value in the format set in the Data Format category in Tool Properties when you specify a type (so DbVisualizer can interpret the value when it turns it into a Date value to be passed to the database. I'm not suggesting you change anything, but what could work would be to remove the type specification for the variable and specify the value as dd-MMM-yyyy. If the database accepts this format for a Date literal, you would not need to change the format for the ExcelIntroText. Another way it could be done is to keep the Date type in the variable and specify the value as dd-MMM-yyyy, but change the Date format to dd-MMM-yyyy in Tool Properties. Then DbVisualizer would be able to pass it as a Date to the database but use the literal value in ExcelIntroText. Either way, in the next maintenance release you will be able to just use the format option with your variable in the ExcelIntroText. I hope this made it a bit clearer. Best Regards, Hans

> {quote:title=Hans Bergsten wrote:}{quote} > ... > Either way, in the next maintenance release you will be able to just use the format option with your variable in the ExcelIntroText. > > I hope this made it a bit clearer. Yes it did, although I think that having the option to use a format clause to control the display format for variables of type Date will help make it less confusing. Thanks.

> {quote:title=Roger Bjarevall wrote:}{quote} >We have just released 9.2.6 with a fix based on the latest response by Hans I had a chance to test this today and it works great. Thanks guys!