topic Re: Convert minutes to timestamp in QlikView

Convert minutes to timestamp

Mon, 21 Apr 2014 12:34:11 GMT

I need to create a datetime from data that is stored in two separate field. The date field is stored as a datetime, but the time part is always 00:00:00 so I need to extract just the date. The time field is stored as minutes after midnight, i.e. 630 = 10:30:00 AM.

Re: Convert minutes to timestamp

Mon, 21 Apr 2014 12:39:03 GMT

Karen Gerdes wrote:

I need to create a datetime from data that is stored in two separate field. The date field is stored as a datetime, but the time part is always 00:00:00 so I need to extract just the date. The time field is stored as minutes after midnight, i.e. 630 = 10:30:00 AM.

Re: Convert minutes to timestamp

tresB — Mon, 21 Apr 2014 12:39:38 GMT

If you need just the date, should not be bothered about time part, try like:

Load

Date(Floor(YourDateField)) as NewDate

Re: Convert minutes to timestamp

maxgro — Mon, 21 Apr 2014 12:39:49 GMT

=datetimefield + timeinminutesfield / (24*60)

Re: Convert minutes to timestamp

Mon, 21 Apr 2014 12:42:06 GMT

datetime.adjust(string.datetime(date,"MMDDCCYY"),1,"Y")

datetime.adjust(srting.datetime(date,"MMDDCCYY"),1,"Y",false)

Re: Convert minutes to timestamp

Mon, 21 Apr 2014 12:49:13 GMT

Hi,

Load like this

T1:

Load * Inline [
Time
630
];

T2:
Load Time,
Timestamp('00:'&Time,'hh:mm:ss[.fff] TT') as Hours
Resident T1;
Drop Table T1;

Hope it helps you..

Re: Convert minutes to timestamp

Mon, 21 Apr 2014 12:59:51 GMT

Hi Karen,

you can try the Time Function.

e.g. Time(A) where A=630

Re: Convert minutes to timestamp

Mon, 21 Apr 2014 14:14:03 GMT

Thank you for all the help. It looks like this worked:

timestamp#

(Date(Floor(start_date))&' '& Timestamp('00:'&start_time,'hh:mm:ssTT'),'YYYY-MM-DD hh:mm:ss')