Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Converting date/timestamp

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Converting date/timestamp

LOAD

...

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

...

5 Replies
MVP
MVP

Re: Converting date/timestamp

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

Not applicable

Re: Converting date/timestamp

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

christian77
Valued Contributor

Re: Converting date/timestamp

Hi:

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

or

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!

Not applicable

Re: Converting date/timestamp

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.

MVP
MVP

Re: Converting date/timestamp

LOAD

...

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

...

Community Browser