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

[#1]

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.

[#2]

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]

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]

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]

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;

[jonathandienst]

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)