Convert Datefield

[fred_s]

I'm a newbie. Don't be too hard... [:)]

We have this weird kind of database, no sql, mysql or whatever.
This database supports ODBC, but only the basic stuff.
For most fields no problem, except Date fields.

Every Date comes in as a String (but documentprops says it's a date...).
Set Dateformat in script is correct ('YYYY-M-D').

"month (DateX) as Datex_month" doesn't do his job.

Question 1:
Is there a possibility, after (Re)loading the data, to convert this field (maybe to a new and real Date field?)?

Any ideas or Workarounds?

Question 2:
Most of the Qlikview examples split dates in to year, month and day.
Since I can't split these fields, is there a clever way to select Dates?
We can use * search for text and <=> for Numeric fields, but I have no idea how to select Dates.
So far no luck with > or between functions

Thanks in advance

Fred

[#2]

What yyyymmdd format does your "date" field pull in? Try using the date() and date#() functions to convert your text field into a true date field in qlikview.

[#3]

Hi Fred,

if it is a text field than write:

Date(Date#(DateX, 'YYYY-M-D'), 'DD-MM-YYYY') as MyDateFormat

Date# is for interpretation and Date to format your data.

If it does the job you can split it to YEAR and so on as follows:

Year(MyDateFormat) as Year,

Month(MyDateFormat) as Month

If it doesn´t work please provide some data as example.

Good luck!

Rainer

[fred_s]

Actually I've tried this one, but it seems like
"Date(Date#(DateX, 'YYYY-M-D'), 'DD-MM-YYYY') as MyDateFormat" is not supported:

SQL Error:Syntax error or access violation
SQL Scriptline:
SQL State:37000

The fact that these Date/Date# functions aren't supported is probably my problem.

The database returns Dates in 'Dutch' format (DD-MM-YYYY) so I've changed the DateFormat in the script back to Dutch instead of YYYY-M-D.

As mentioned earlier, my dates are probably string (left alignment in Qlikview where Excel example dates have Right alignment).
According to documentproperties it should be a Date.

I have a little example attached, where Factuurdat is the problem.
Datum, mnd and Omschr are fields from an Excel file, just for me to show that it is possible to split Dates.

Hope you guys can offer me a workaround...

Thanks!

[GabrielAraya]

Hi, the Date# is a QV function, I recomend:

Load

Date(Date#( .... )

;

Sql Select ........ ;

Gabriel

[fred_s]

Hi Gabriel,

Thanks for your reply.

The problem is, the SQL string in QV is some kind of Pass Through-query like the ones in MS Access.
QV doesn't do anything with the string, just passes it through.

The problem is 'Date' or 'Date#' are no QV functions, but part of the (Pass Through) SQL string.
It seems my ODBC connection does not support Date functions.

[GabrielAraya · Accepted solution]

Hi fred,

I don't thing that the problem is with the connection string .. (ODBC ..... or OLEDB ... ), my suggestion was to work with a LOAD before the SQL statement, the reason is the Date# is a QlikView function ...so:
Load
Field1,
Field2,
Date(Date#(field3,'DD-MM-YYYY')) as field3
;
Sql Select Field1, Field2, Field3 from AccesTable;

Gabriel

[fred_s]

Hi Gabriel,

It took me a couple of seconds to get your point, but this looks great!!

Can you tell me how to split this Date into Year, Month and Day?
Something like
Year(Factdatum) as Factjaar

It doesn't work in the load and it doesn't in the SQL string.

Thanks!
It's already a great solution and still can get better...

Current code:

ODBC CONNECT TO SERVERX (XUserId is 5151515151515, XPassword is 7474747474747);
Load
 Boekjaar,
 [Factuur-nr],
 Factuurbedr,
 Date(Date#(Factuurdat,'DD-MM-YYYY')) as Factdatum
;
SQL SELECT Boekjaar,
 Factuur-nr ,
 Factuurbedr,
 Factuurdat
FROM _DF_Debiteur_factuur ;

[GabrielAraya]

Of Course, it is very simple, you can add a new line after Date(Date#(.......) as Factdatum,

Year(Date#(.....)) as YearFactdatum,
Month(Date#(...)) as MonthFactdatum .. and so on

Gabriel

[rwunderlich]

You may also use an additional preceeding load to avoid repeating the date#() function. Like this:

LOAD *,
year(Factdatum) as YearFactdatum,
month(Factdatum) as MonthFactdatum
;
Load
Boekjaar,
[Factuur-nr],
Factuurbedr,
Date(Date#(Factuurdat,'DD-MM-YYYY')) as Factdatum
;
SQL SELECT Boekjaar,
Factuur-nr ,
Factuurbedr,
Factuurdat
FROM _DF_Debiteur_factuur ;

-Rob