Converting date/timestamp

[#1]

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!

[swuehl]

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

[#3]

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]

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!

[#5]

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.

[swuehl · Accepted solution]

LOAD

...

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

...