topic Re: Converting date/timestamp in QlikView

Converting date/timestamp

Mon, 25 Jun 2012 15:51:21 GMT

LOAD

UserName,
FirstName,
LastName,
Title,
OU,
Department,
Location,
Emailaddress,
WhenCreated,
PaswordLastChanged,
LastLogonTime,
AccControl,
Status,
PasswordAge,
AccountLockout,
AccountExpirationDate,
NextPwdReset,
MFID,
Description,
Manager,
SSO,
"NON_Human";

SQL
SELECT UserName,
FirstName,
LastName,
Title,
OU,
Department,
Location,
Emailaddress,
WhenCreated,
PaswordLastChanged,
LastLogonTime,
AccControl,
Status,
PasswordAge,
AccountLockout,
AccountExpirationDate,
NextPwdReset,
MFID,
Description,
Manager,
SSO,
"NON_Human"
FROM

I have a field that contains a date and a timestamp. Its the "LastLogonTime" field. I want to convert the timestamp in all of them to the same thing, all zero's. I want to do it in the script.

After I get that done I want to compare dates. So flag dates that are older than 60 days or 180 days. I want to do that in the front end.

Thank you!

Re: Converting date/timestamp

swuehl — Mon, 25 Jun 2012 15:56:02 GMT

You need to first ensure that your timestamp (date and time) is interpreted correctly by QV. Easiest way is to set the standard timestamp format variable at the top of your script to the format needed.

Then use

LOAD

...

daystart(LastLogonTime) as LastLogonDateOnly

....

to create a date only field from your timestamp (time set to 00:00:00).

Hope this helps,

Stefan

Re: Converting date/timestamp

Mon, 25 Jun 2012 15:57:18 GMT

Hi ethanjbeau,

Can you try to ltrim or left functions to acquire date from those log fields and add zero's at the end of that field and name it as new field which helps you to compare.

left(logfield,10) & '00-00-00' as comparefield.

use left (logfield,8) if you are having year as 2 char's

hope this helps and let me know if i got it in a wrong way

thank you

Meher

Re: Converting date/timestamp

christian77 — Mon, 25 Jun 2012 15:59:29 GMT

Hi:

date(field,'YYYY/MM/DD')

makedate(year(Field),month(field),day(field))

look into date funcktions or format funcktions.

You can also go to doc properties, number, and decide the format for each field, but I preffer to bring it already short to save some memory.

Bring date fields as dates.

luck!

Re: Converting date/timestamp

Mon, 25 Jun 2012 17:04:55 GMT

What if I wanted to find out how old certain dates were?

For example, today is 06/25/2012. I want to find any dates between todays today and 60 days ago.

Re: Converting date/timestamp

swuehl — Mon, 25 Jun 2012 17:10:38 GMT

LOAD

...

if(LastLogonTime < today()-60, 'older than 60 days','within last 60 days') as LogOnClass,

...