topic Re: Create a timestamp from two string fields in App Development

Create a timestamp from two string fields

Tue, 12 May 2015 22:21:39 GMT

Let me start by saying I'm using Qlik Sense. I have a date field and a time field. Both of these are stored as six character strings in the database. I want to take them and make them a single date/time field. Examples below.

Date	Time	Result
051215	013313	05-12-2015 01:33:13
121314	233245	12-13-2014 23:32:45

I've tried something something like this to make it work but it's just now working out for me:

Timestamp#(Date#("date",'MMDDYY')&' '&Time#("time",'HHMMSS'), 'MM-DD-YYYY HH:MM:SS)

The above expression gives me values that look like:

40461 205645 (10/10/2010 20:56:45)

40462 214058 (10/11/2010 21:40:58)

42136 163018 (5/12/2015 16:30:18)

What am I doing wrong???

I realize the number in place of the date is the number of days since the epoch but for whatever reason, it's not generating a number like 42136.687518

Re: Create a timestamp from two string fields

maxgro — Tue, 12 May 2015 22:34:09 GMT

LOAD Date,

Time,

Result,

timestamp(Date#(Date, 'MMDDYY') + Time#(Time, 'hhmmss')) as New

FROM

[https://community.qlik.com/thread/163720]

(html, codepage is 1252, embedded labels, table is @1);

Re: Create a timestamp from two string fields

Tue, 12 May 2015 22:41:46 GMT

I changed my LOAD statement to this and now no data comes up after the load completes:

LOAD "date",

"time",

timestamp(date#("date",'MMDDYY') + time#("time",'hhmmss')) as RecvDateTime;

SQL...

Re: Create a timestamp from two string fields

sunny_talwar — Wed, 13 May 2015 00:16:56 GMT

Try this may be:

Table:

LOAD Date,

Time,

Result,

Timestamp(timestamp#(Date&Time, 'MMDDYYhhmmss'), 'MM-DD-YYYY hh:mm:ss') as New

FROM

[https://community.qlik.com/thread/163720]

(html, codepage is 1252, embedded labels, table is @1);

Re: Create a timestamp from two string fields

Wed, 13 May 2015 14:06:57 GMT

Well. I had my head stuck up my back side for this one....

Turns out, my date field is a DateTime field coming in from the SQL server...not just a string.

So here's what I ended up with which (so far) appears to be working correctly:

timestamp#(date("date")&' '&"time", 'MM/DD/YYYY HHMMSS') as RecvDateTime

Re: Create a timestamp from two string fields

sunny_talwar — Wed, 13 May 2015 14:08:26 GMT

Hahahaha, at least you found a solution.

Best,

Sunny