Convert string YYYYMMDD to MM/DD/YYYY and string hhmmss to HH hours only and combine both to one field in script. Please help.

[haneeshmarella]

Hi, I have two dimensions i.e Date and Time in string formats.

Date field is PRDCR in string format of YYYYMMDD

Time field is PRTCR in string format of hhmmss

I would like to convert PRDCR to MM/DD/YYYY and PRTCR to HH (hours only) format and combine both fields as a new timestamp field in the form of MM/DD/YYYY HH (ex. 06/11/2018 22) where 22 is 10 PM.

Please guide me on how to do so. I did the following:

PRDCR --> Date(Date#("PRDCR ",'YYYYMMDD'),'MM/DD/YYYY')

PRTCR --> (Time(Time#("PRTCR ",'hhmmss'),'HH')&':00') --> I feel this is wrong because it gives me two rows 00:00 and :00 which is not right.

[sunny_talwar]

May be this

TimeStamp(TimeStamp#(PRDCR&PRTCR, 'YYYYMMDDhhmmss'), 'MM/DD/YYYY hh') as FieldName

or this

TimeStamp(Floor(TimeStamp#(PRDCR&PRTCR, 'YYYYMMDDhhmmss'), 1/24), 'MM/DD/YYYY hh') as FieldName

[haneeshmarella]

Thank you Sunny.

How do I filter data to be refreshed everyday, within the script since the field PRDCR (YYYYMMDD) is a string, I would like to automatically filter the data with today's date (today()). How do I do this within the SQL script and not under load, as there is huge amount of data to load if implemented under load.

[sunny_talwar]

Use a where clause in your SQL query to restrict the data. This might be something that a dba might be able to help you better as databases can vary on how this can be structured... for example, in ORACLE I would do something like this

WHERE To_Date(PRDCR, 'YYYYMMDD') = SYSDATE;