Show only valid times from Date/Time field

[#1]

Hi everyone,

I am trying to work out how I can have a field in my load script which filters out midnight times.

The field is a date time stamp from SQL which if left blank canbe either blank (NULL) or be the date with a midnight time. What I would like to do is strip the date away and leave the time, only if the time is not midnight (the chances of it actually being midngiht are quite remote in this case).

Does anyone have a suggestion?

Many thanks in advance. 

[swuehl · Accepted solution]

Maybe

...

if(frac(DATETIMESTAMP)>0, time#(time(DATETIMESTAMP))) as TIME,

...

Hope this helps,

Stefan

[#3]

Hi Stefan,

Thanks for the expression, I have tested it on a table and it works great. I don't think I would have been able to work that one out on my own.

Many thanks

Regards

[Jason_Michaelides]

Phil - don't forget to mark Stefan's answer as correct.

Jason