Getting a date from a column in a select, then using it in a text object after reformatting it

[#1]

Hi, I would like to select the top record from a SQL Server table, like SELECT TOP 1 Date FROM Table_X, and assign that value to a variable, then pull the month out from the date, and then for lack of a better word, substring the first three letters of the month, and then apply that three letter word to a text object field... anyone know how this can be done?

Thanks!

[#2]

So something like 04/01/2014 would become -> April -> APR

[simenkg]

Upper(text(month(Date))) will give you that. If that gives you APRIL, then use

Left(Upper(text(month(Date))),3)

[#4]

Ok great, what abou the load script. How would I pull a top value from a tablecolumn and assign it to a variable?

[simenkg]

Depends on how you mean.

If you have a table like

Dates:

load Date from Table1 order by Date desc;

then you can use

let vDate = peek('Date',0,'Dates');

Should give you the highest Date. If it gives you the lowest change desc to asc.

If you really ONLY need the top date, then you can load a date like this.

Dates:

Load max(Date) as MaxDate from Table1;

let vDate = peek('MaxDate',0,'Dates');

[ashfaq_haseeb]

Hi

After you made connection string with database

same SQL will work

SELECT TOP 1 Date FROM Table_X

Later use peek function to hold a value to variable.

Regards

ASHFAQ

[#7]

Ok I must be missing something, here's what I got:

  Date:

SQL SELECT TOP 1 REPORT_DATE
FROM Sales;
LET vMonth = peek('REPORT_DATE',0,'Date');

When I step thru the load, the value of vMonth is null, but it shows one record has been loaded into the table

[ashfaq_haseeb]

Hi try below

LET vMonth = peek('REPORT_DATE',-1,'Date');