Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to concatenate a date and time field into one field.

Hi All,

i need to concatenate two fields, one is date and other is time. And the resultant filed should be in the format 'MM/DD/YYYY hh:mm:ss'

date :5/52015

Time: 23:15:46

result: 5/52015 23:15:46

thanks in Advance.

Tags (1)
5 Replies
Not applicable

Re: How to concatenate a date and time field into one field.

Hi,

Try the following code:

LOAD *,Date#(Date&chr(32)&Time,'MM/DD/YYYY hh:mm:ss') As DateT;

LOAD * INLINE [

    Date, Time

    05/05/2015, 23:15:46

    07/12/2014, 20:16:54

];

svenkita
Contributor II

Re: How to concatenate a date and time field into one field.

Hope This Helps. Attached is the sample file

Load *,TimeStamp#(Text(Date(myDate,'MM/DD/YYYY')) & ' ' & Text(Time(myTime,'hh:mm:ss')),'MM/DD/YYYY hh:mm:ss') as newDateInTimeStampFormat,
Text(Date(myDate,'MM/DD/YYYY')) & ' ' & Text(Time(myTime,'hh:mm:ss')) as newDateInTextFormat;
LOAD * INLINE [
myDate, myTime
5/5/2015, 23:15:46
]
;

vardhancse
Valued Contributor II

Re: How to concatenate a date and time field into one field.

Table:

LOAD * INLINE [

    Date, Time

    02/07/2015, 10:20:00

    02/07/2015, 10:21:00

    02/07/2015, 10:22:00

    02/07/2015, 10:23:00

];

Fact:

LOAD

(Date and Time) as Date

Resident Table;

Drop Table Table;

We can change the Date field format as Timestamp

satyapaleti
Contributor II

Re: How to concatenate a date and time field into one field.

Hi Saritha,

Try this

Time:

LOAD * INLINE [

    Date, Time

    05/05/2015, 23:15:46

    07/12/2014, 20:16:54

];

Join(Time)

LOAD *,Date#(Date&chr(32)&Time,'MM/DD/YYYY hh:mm:ss') As DateTime

Resident Time;

MVP
MVP

Re: How to concatenate a date and time field into one field.

It is generally better to not do this - the experts go further and say when you receive a date/time value you should split it into separate date and time fields. This is because of the way QV stores the in-memory data.

See this: Symbol Tables and Bit-Stuffed Pointers

Of course, if you need to do this as a part of your load to perform some sort of caclulation, and the date and time are normal QV date and time values, then you can simply add them together to get a date/time value:

     TimeStamp(myDate + myTime, 'YYYY/MM/DD hh:mm:ss')

(Time stamp allows you to decide on the display format and has no effect on the underlying value)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser