Date and Datetime query

[Patreson31]

Hi folks,

I'm having real trouble with some csv input data, tried a variety of approaches convert the fields 'Datestamp' to 'DD/MM/YYYY' and 'Period Ending' to 'DD/MM/YYYY hh:mm:ss' fields in the script to then save as a qvd - the results are pretty wacky and despite reading a ruck of forums I can't seem to get the desired output. 

The incoming data is as a datestamp for both fields as follows:

The scripting I've attempted is as follows:

And the output is... well messy:

Any suggestions would be really appreciated!

Thanks in advance!

[Arthur_Fong]

Try this:

LEFT(DATESTAMP,INDEX(DATESTAMP,' ')) AS DATESTAMP,

TIMESTAMP#([PERIOD ENDING],'DD/MM/YYYY hh:mm')AS [PERIOD ENDING]

[Patreson31]

Hi there Arthur,

Thanks for that, we're 96% of the way there! Unfortunately midnight is still returning not as intended - any thoughts?

[Arthur_Fong · Accepted solution]

Try this:

if(len(DATESTAMP)=5,

date(num#(text(DATESTAMP)),'DD/MM/YYYY'),

LEFT(DATESTAMP,INDEX(DATESTAMP,' '))) AS DATESTAMP,
IF(len([PERIOD ENDING])=10,TIMESTAMP#(TEXT([PERIOD ENDING])&' 00:00','DD/MM/YYYY hh:mm')
,TIMESTAMP#([PERIOD ENDING],'DD/MM/YYYY hh:mm'))AS [PERIOD ENDING]