I’m trying to convert a sample 12 hour date with AM/PM into a timestamp, however, Snowflake is throwing an error that it’s not parsable: SELECT TO_TIMESTAMP(‘7/16/2021 4:52:25 AM’, ‘MM/dd/yyyy HH12:mm:ss AM’) The error message returned: Can’t parse ‘7/16/2021 4:…
Tag: sql
query to get customer list using JOIN with sum () of the amounts spent in orders
I have the following tables table anag (customer registry) table levels (table that connects each customer to his salesperson. For database registration reasons, the link between customer and seller is given by the customer’s telephone number) table orders (contains all purchases made by customers, of c…
Finding the first empty column in a data row
I saw similar questions and most turned into arguments about table design, normalization, etc. Not all of us have the luxury of making our clients change their database designs. Here is my dilemma. My client asks that I make it possible for their workers to be able to add call out time for the preceding day. …
ORACLE json_object_t – error when call get_string() method on object got by chain of methods .get_array() .get()
I am trying to get a value from an array contained in JSON-object by chain of methods of JSON_OBJECT_T type – .get_array(…).get(0).get_string(…) But getting an exception component ‘GET_STRING’ must be declared Here is my code, could someone explain why the chain does not work? An…
Triggers data retention in inserted table and combination of update and insert on one column
I have a table in SQL Server which houses snapshots of all data to track changes in status of the source table tbl_D_project. If this status changes or a new record is added in tbl_D_project, a trigger should start and the line will be copied to the snapshot table. I wrote two triggers based on another questi…
SQL – Replace characters in a SQL string
I would like to know how to replace all characters between 2 dashes (-) and replace the dashes as well. It should be in a single select statement, not a loop. The text between the dashes can be variable length and any character. The characters before and after the dashes can also be variable length. Answer Yo…
How to use LIMIT to sample rows dynamically
I have a table as follows: SampleReq Group ID 2 1 _001 2 1 _002 2 1 _003 1 2 _004 1 2 _005 1 2 _006 I want my query to IDs based on the column SampleReq, resulting in the following output: Group ID 1 _001 1 _003 2 _006 The query should pick any 2 IDs from group
Want to convert timestamp to date format in hive
want to convert this number ‘20210412070422’ to date format ‘2021-04-12’ in hive I am trying but this returns null value from_unixtime(unix_timestamp(eap_as_of_dt, ‘MM/dd/yyyy’)) Answer The best methoid is to do without unix_timestamp/from_unixtime if possible and in your c…
Why ‘ in ‘ is so much faster than ‘ = ‘ in SQL Select?
This select’s execution time is approximately 25 ~ 30 sec. Execution plan for ‘ = ‘: But if I change ‘ = ‘ for ‘ in ‘, then it becomes so much faster about 0.040 ~ 0.060-sec average. Execution plan for ‘ in ‘: And there have been opposite cases like this, …
How can I check if the ID’s of a table match another ID’s of another table in SQL
So I have 4 parameters, one of them its given as an input parameter of a stored procedure (its like a code that represents an unique action), and I need to match these ID’s with another ID’s in another table, so I need to check in table 2 the ID’s “DeliverTypeID”, “Schedule…