How to concatenate date and time values and place results in another field
-
Actually, there is no problem with it. I'm just being a numbty this Friday.
Thanks
-
Is the CONVERT function absolutely necessary?
I've looked at a few CAST examples, and none of them actually include the CONVERT function
-
carlton 84646 wrote:
Is the CONVERT function absolutely necessary?
I've looked at a few CAST examples, and none of them actually include the CONVERT function
In SQL Server, CAST and CONVERT are identical (except in form) for datatype conversions. Convert has the added advantage of being able to apply a format style ID if wanted or needed.
<EDIT>: The biggest difference is that most SQL Engines don't have a CONVERT function. They use other things to format data. Most do have a CAST. That and personal preference. I like CONVERT because it does everything I might need and so I don't have both CONVERT and CAST in my code. It also aggravates the hell out of people that believe that EVERYTHING must be 100% "portable" across the big 4 database engines, whicb is perfect to make my point about how being portable will actually go to hell in a hand-basket as soon as you try to do anything with variables, stored procedures, functions, etc. You know... the useful stuff. 😀
--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 -
Jeff,
As regards the CONVERT function, you make an excellent point. I use a dialect of T-SQL called sparkSQL (which comes from the world of Apache Spark.
I am trying to convert the code you and others from this community kindly provided to sparkSQL as follows
test = spark.sql("""select CAST(LAST_MOD_DATE AS STRING) + ' ' + CAST(DATE_ADD(SECOND, LAST_MOD_TIME % 100 + LAST_MOD_TIME / 100 % 100 * 60 + LAST_MOD_TIME / 10000 * 3600, 0), 8 AS STRING ) AS expr1 from DmWo""")However, I'm getting error:
I appreciate you might not be familiar with this SQL syntax but you have any thoughts it would be appreciated -
Basically, I think I need to find a way of achieving the result without the CONVERT function
-
So,
I managed to get the first half of the code to work in sparkSQL as follows:
select CAST(LAST_MOD_DATE AS STRING) AS expr1 from DmWo
I then try and get the second half of the code to work as follows:
select DATE_ADD(SECOND, LAST_MOD_TIME % 100 + LAST_MOD_TIME / 100 % 100 * 60 + LAST_MOD_TIME / 10000 * 3600, 0), 8) from DmWo
However, I get the following error:
mismatched input ')' expecting {<EOF>, ';'}(line 1, pos 114)Any thoughts
-
carlton 84646 wrote:
So,
I managed to get the first half of the code to work in sparkSQL as follows:
select CAST(LAST_MOD_DATE AS STRING) AS expr1 from DmWo
I then try and get the second half of the code to work as follows:
select DATE_ADD(SECOND, LAST_MOD_TIME % 100 + LAST_MOD_TIME / 100 % 100 * 60 + LAST_MOD_TIME / 10000 * 3600, 0), 8) from DmWo
However, I get the following error:
mismatched input ')' expecting {<EOF>, ';'}(line 1, pos 114)Any thoughts
Quick thought, on sparkSQL use the to_timestamp function to convert a string to timestamp 😉
😎
-
Eirikur, you're in the right ball park,
In typical pyspark I could do the following:
.withColumn("LAST_MODIFICATION_DT", to_timestamp(concat(col('LAST_MOD_DATE'), lit(' '), col('LAST_MOD_TIME')), 'yyyyMMdd HHmmss'))
But I'm not sure how to add that to sparkSQL.
I'm going to try
select to_timestamp(concat(col('LAST_MOD_DATE'), lit(' '), col('LAST_MOD_TIME')), 'yyyyMMdd HHmmss')) as Last_Modification_DT
and see if that works
-
carlton 84646 wrote:
Jeff,
As regards the CONVERT function, you make an excellent point. I use a dialect of T-SQL called sparkSQL (which comes from the world of Apache Spark.
I am trying to convert the code you and others from this community kindly provided to sparkSQL as follows
test = spark.sql("""select CAST(LAST_MOD_DATE AS STRING) + ' ' + CAST(DATE_ADD(SECOND, LAST_MOD_TIME % 100 + LAST_MOD_TIME / 100 % 100 * 60 + LAST_MOD_TIME / 10000 * 3600, 0), 8 AS STRING ) AS expr1 from DmWo""")However, I'm getting error:
I appreciate you might not be familiar with this SQL syntax but you have any thoughts it would be appreciatedAt this point, I'm quite confused... You said everything was working fine. When did this become a SparkSQL issue and what makes you thing that SparkSQL is a dialect of T-SQL? That's a bit like saying that Postgres or Oracle PL/SQL is a dialect of T-SQL. 😉
--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 -
carlton 84646 wrote:
Eirikur, you're in the right ball park,
In typical pyspark I could do the following:
.withColumn("LAST_MODIFICATION_DT", to_timestamp(concat(col('LAST_MOD_DATE'), lit(' '), col('LAST_MOD_TIME')), 'yyyyMMdd HHmmss'))
But I'm not sure how to add that to sparkSQL.
I'm going to try
select to_timestamp(concat(col('LAST_MOD_DATE'), lit(' '), col('LAST_MOD_TIME')), 'yyyyMMdd HHmmss')) as Last_Modification_DT
and see if that works
This might help
https://people.apache.org/~pwendell/spark-nightly/spark-master-docs/latest/api/sql/index.html
😎
-
Jeff Moden wrote:carlton 84646 wrote:
Jeff,
As regards the CONVERT function, you make an excellent point. I use a dialect of T-SQL called sparkSQL (which comes from the world of Apache Spark.
I am trying to convert the code you and others from this community kindly provided to sparkSQL as follows
test = spark.sql("""select CAST(LAST_MOD_DATE AS STRING) + ' ' + CAST(DATE_ADD(SECOND, LAST_MOD_TIME % 100 + LAST_MOD_TIME / 100 % 100 * 60 + LAST_MOD_TIME / 10000 * 3600, 0), 8 AS STRING ) AS expr1 from DmWo""")However, I'm getting error:
I appreciate you might not be familiar with this SQL syntax but you have any thoughts it would be appreciatedAt this point, I'm quite confused... You said everything was working fine. When did this become a SparkSQL issue and what makes you thing that SparkSQL is a dialect of T-SQL? That's a bit like saying that Postgres or Oracle PL/SQL is a dialect of T-SQL. 😉
He he, just like going to a restaurant, wanting a steak, ordering a turkey and getting pork chops cooked as potatoes, familiar feeling 🙂
😎
-
carlton 84646 wrote:
So,
I managed to get the first half of the code to work in sparkSQL as follows:
select CAST(LAST_MOD_DATE AS STRING) AS expr1 from DmWo
I then try and get the second half of the code to work as follows:
select DATE_ADD(SECOND, LAST_MOD_TIME % 100 + LAST_MOD_TIME / 100 % 100 * 60 + LAST_MOD_TIME / 10000 * 3600, 0), 8) from DmWo
However, I get the following error:
mismatched input ')' expecting {<EOF>, ';'}(line 1, pos 114)Any thoughts
You have an extra part to the statement. The portion ', 8)' does not have a corresponding open - so it fails. I also noticed that you are adding the seconds to '0' - and I am assuming that at some later point you are trying to concatenate with a date. Why not use the existing date LAST_MOD_DATE?
select DATE_ADD(SECOND, LAST_MOD_TIME % 100 + LAST_MOD_TIME / 100 % 100 * 60 + LAST_MOD_TIME / 10000 * 3600, LAST_MOD_DATE) from DmWo
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.How to post questions to get better answers faster
Managing Transaction Logs -
Hi Jeff,
Thanks for sticking with me on this.
Just a couple of things:
- What is the relevance of SECOND?
- I also need to CAST LAST_MOD_TIME.
Can you provide the answers to the above?
-
carlton 84646 wrote:
Hi Jeff,
Thanks for sticking with me on this.
Just a couple of things:
- What is the relevance of SECOND?
- I also need to CAST LAST_MOD_TIME.
Can you provide the answers to the above?
That won't work in Spark SQL. Regardless of the version of SparkSQL you're using, DATE_ADD() in Spark SQL will ONLY add a whole number of days. There are no other datepart options to the function. Please see the following reference, which contains the "latest" documentation for Spark SQL functions.
https://spark.apache.org/docs/latest/api/sql/index.html#date_add
IF you using Spark SQL 3.0 (and up), there is some new functionality that that works in a manner similar to the various DATETIMEFROMPARTS() function in T-SQL and (apparently), you could use nearly identical methods to the code I posted for T-SQL in Spark SQL. Here's the documentation on that. I'll let you do the conversion because I don't have Spark SQL loaded on my machine.
https://spark.apache.org/docs/latest/api/sql/index.html#make_timestamp
--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 -
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.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.How to post questions to get better answers faster
Managing Transaction Logs
I appreciate you might not be familiar with this SQL syntax but you have any thoughts it would be appreciatedViewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply