How to concatenate date and time values and place results in another field

Question

How to concatenate date and time values and place results in another field

Viewing 3 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Jeffrey Williams wrote:

So going all the way back to the beginning - using sparkSQL instead of T-SQL, what you need to do is convert your integer time to a string in the format HH:MM:SS and convert your date to a string in the format YYYY-MM-DD. Concatenate those together into the format 'YYYY-MM-DD HH:MM:SS' and use the to_timestamp function to convert that string to a valid timestamp.
Since none of this is anything that we would do in T-SQL it is not something where we can provide any real help.

Not quite true. The concept of integer division and modulo both exist in Spark SQL and, if the OP has Spark 3.0, they have that MAKE_TIMESTAMP function, which is nearly identical to DATETIMEFROMPARTS.

See my previous post just above yours for links.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 2

Jeff Moden wrote:

Jeffrey Williams wrote:
So going all the way back to the beginning - using sparkSQL instead of T-SQL, what you need to do is convert your integer time to a string in the format HH:MM:SS and convert your date to a string in the format YYYY-MM-DD. Concatenate those together into the format 'YYYY-MM-DD HH:MM:SS' and use the to_timestamp function to convert that string to a valid timestamp.
Since none of this is anything that we would do in T-SQL it is not something where we can provide any real help.
Not quite true. The concept of integer division and modulo both exist in Spark SQL and, if the OP has Spark 3.0, they have that MAKE_TIMESTAMP function, which is nearly identical to DATETIMEFROMPARTS.
See my previous post just above yours for links.

That is going to just as difficult - if not more so as you have to first get the year, month, day, hour, minute and second from the integer values to pass to MAKE_TIMESTAMP.

However - using TO_TIMESTAMP with the format option, it should just be: TO_TIMESTAMP(CONCAT_WS(' ', CAST(LAST_MOD_DATE AS char(8)), CAST(LAST_MOD_TIME AS char(6))), 'yyyyMMdd HHmmss')

Validate the format - it should be correct. You could remove the separator and just use CONCAT without the space in the format.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Jeffrey Williams wrote:

Jeff Moden wrote:
Jeffrey Williams wrote:
So going all the way back to the beginning - using sparkSQL instead of T-SQL, what you need to do is convert your integer time to a string in the format HH:MM:SS and convert your date to a string in the format YYYY-MM-DD. Concatenate those together into the format 'YYYY-MM-DD HH:MM:SS' and use the to_timestamp function to convert that string to a valid timestamp.
Since none of this is anything that we would do in T-SQL it is not something where we can provide any real help.
Not quite true. The concept of integer division and modulo both exist in Spark SQL and, if the OP has Spark 3.0, they have that MAKE_TIMESTAMP function, which is nearly identical to DATETIMEFROMPARTS.
See my previous post just above yours for links.
That is going to just as difficult - if not more so as you have to first get the year, month, day, hour, minute and second from the integer values to pass to MAKE_TIMESTAMP.
However - using TO_TIMESTAMP with the format option, it should just be: TO_TIMESTAMP(CONCAT_WS(' ', CAST(LAST_MOD_DATE AS char(8)), CAST(LAST_MOD_TIME AS char(6))), 'yyyyMMdd HHmmss')
Validate the format - it should be correct. You could remove the separator and just use CONCAT without the space in the format.

If you do it with the integer math that I used in the T-SQL method I previously posted, it's not that much code and it blows away any character based transformations.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions