
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Convert string YYYYMMDD to MM/DD/YYYY and string hhmmss to HH hours only and combine both to one field in script. Please help.
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
