String Conversion to Date, Time, Timestamp in certain format

[haneeshmarella]

Hi guys, I am pretty new to Qlikview. Please help with below question.

I have two string fields for date and time.

DATE_CREATED: yyyymmdd

TIME_CREATED: hhmmss

I converted date string to Date format using

Date(Date#("DATE_CREATED",'YYYYMMDD'),'MM/DD/YYYY')

I converted time string to time format using (not sure if this is correct)

Time(Time#("TIME_CREATED",'HHMMSS'),'HH:MM:SS TT')

My requirement is:

1. Convert the time above to just hours only. ie. If 11:20:02 AM has count 10 and 11:30:32 AM has count 20, I want both to be 11 AM and be a single row adding the calculation for that field into one i.e 30.

2. After getting the hours format above, I would like to concatenate the DATE_CREATED and TIME_CREATED into one field. For this, I have used the below but I get results with future times and dates also which is wrong.

TimeStamp(Floor(TimeStamp#(DATE_CREATED&TIME_CREATED, 'YYYYMMDDhhmmss'), 1/24), 'MM/DD/YYYY hh') as Date_Time

and

Timestamp(Date#("DATE_CREATED"&"TIME_CREATED",'YYYYMMDD HHMMSS'),'MM/DD/YYYY HH TT') AS Date_Time

3. After concatenating above in step 2, I would get the time in EST. I would like to make it Central Time by subtracting 1 hour to the result. Please let me know how to do it.

Please help guiding me in the above 3 questions.

[shiveshsingh]

This can help for your first problem

Time(Time#("TIME_CREATED",'hhmmss'),'hh') as Hour

[Anil_Babu_Samineni]

What if you do something like this?

Date(Date#("DATE_CREATED",'YYYYMMDD'),'MM/DD/YYYY') &

Interval(Time#("TIME_CREATED",'HHMMSS'),'HH') as Final_Date_Format

[haneeshmarella]

I found the solution to get it into MM/DD/YYYY hh:mm:ss format, but is there a way I can round it off to hours?

TimeStamp((TimeStamp#(DATE_CREATED & IF( LEN(TIME_CREATED) = 3 , ('000' & TIME_CREATED), IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,IF( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED, TIME_CREATED ))),'YYYYMMDDhhmmss' )),'MM/DD/YYYY hh:mm:ss') as DATE_TIME_EST

For example, if there are two rows of timestamps:

Timestamp                                        Count of Orders

06/15/2018 14:30:32                                   20

06/15/2018 14:45:02                                   10

                                                                                   Timestamp               Count of Orders

So I want it to be in the format of with the result   06/15/2018 14                     30

I want to convert the above timestamp formula (in my original question) I implemented that results in MM/DD/YYYY hh:mm:ss into the format of MM/DD/YYYY hh with concatenated result.

[Colin-Albert]

The Time function formats the output time, but does not alter the actual Time value held against the record.

To change the actual time value to just minutes or hours, you must use a rounding function such as round(), ceil() or floor().

For example

To round time to the nearest 30 minutes      round(TimeField, 30/(24 *60))

To round down to the nearest 30 minutes      floor(TimeField, 30/(24 *60))

To round up to the nearest 10 minutes          ceil(TimeField, 10/(24 *60))

Have a look at this post for more details The Date Function

[haneeshmarella]

I am not able to implement it to my timestamp. Can you please implement it to the below rounding off the hours, Colin?

TimeStamp((TimeStamp#(DATE_CREATED & IF( LEN(TIME_CREATED) = 3 , ('000' & TIME_CREATED), IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,IF( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED, TIME_CREATED ))),'YYYYMMDDhhmmss' )),'MM/DD/YYYY hh:mm:ss') as DATE_TIME_EST

[haneeshmarella]

I got it Colin. I used the round function the following way and it helped achieve the results.

TimeStamp(floor(TimeStamp#(DATE_CREATED & IF( LEN(TIME_CREATED) = 3 , ('000' & TIME_CREATED), IF  ( LEN(TIME_CREATED) =4  , '00' & TIME_CREATED,IF( LEN(TIME_CREATED) =5  , '0' & TIME_CREATED, TIME_CREATED ))),'YYYYMMDDhhmmss' ),1/24),'MM/DD/YYYY hh') as DATE_TIME_EST

How can I convert the overall output result from EST to Central time by subtracting 1 hour from above formula?

[Colin-Albert]

To remove an hour from the time value, subtract 1/24 from the value.

Subtracting 1 gives a datetime value 1 day earlier, so subtracting 1/24 is a hour earlier

DATE_TIME_CST = DATE_TIME_EST - (1/24) ;