CODEWITHSUNDEEP
sqldb2
oscilatorium
oscilatorium

Reputation: 317

How do I convert a date column to a timestamp in a SQL select statement

I'm trying to write a DB2 query that is selecting a date column and I would like to convert it into a timestamp. For example, the query should take a column that is in a "mm/dd/yyyy" format and convert it to a column that is in a "mm/dd/yyyy hh:mm:ss" format.

For example, if END_DT is a date column in the table, then I would like to select an an additional column that is END_DT but it also has a constant value of hh:mm:ss attached to it. I.e '1/29/2023' should be converted to '1/29/2023 11:22:33' (and so on, for each other END_DT value in each of returned row of the query).

I tried something like this:

SELECT 
    END_DT, TIMESTAMP(END_DT) AS end_dt_with_timestamp 
FROM <table>

but the TIMESTAMP(END_DT) portion of the query does not seem to work.

Upvotes: 0

Views: 2994

Answers (3)

Charles
Charles

Reputation: 23823

You don't mention what platform and version of Db2 you are working with...

But see if your version of the TIMESTAMP() function supports two arguments, with the second argument allowing for

expression-2 must be an expression that returns a value of one of the following built-in data types: a time, a character string, or a graphic string. If expression-2 is a character or graphic string, its value must be a valid string representation of a time. For the valid formats of string representations of dates and times, see “String representations of datetime values” on page 75.

Thus all you need is

SELECT END_DT, TIMESTAMP(END_DT, '11:22:33') as end_dt_with_timestamp 
from <table>

Upvotes: 2

nbk
nbk

Reputation: 49410

you can add to the newly converted timestamp any hour, minutes and second you need

CREATE tABLE t1 (END_DT date)

INSERT INTO t1 VALUES ('29/1/2023' )

SELECT END_DT, TIMESTAMP(END_DT)  + 11 HOUR + 23 MINUTE + 53 SECOND as end_dt_with_timestamp FROM t1
END_DT END_DT_WITH_TIMESTAMP
2023-01-29 2023-01-29 11:23:53

fiddle

Upvotes: 0

Ben Kisow
Ben Kisow

Reputation: 46

You can achieve this by using CONVERT to change datatype to DATETIME and then DATEADD to add your specific timestamp.

Full code for your 1/29/2023 example:

SELECT DATEADD(SECOND, 33, DATEADD(MINUTE, 22, DATEADD(HOUR, 11, CONVERT(DATETIME, '2023-01-29'))))

Without the DATEADD portions, the DATETIME datatype will add a time stamp of '00:00:00.000' automatically.

Upvotes: 0

Related Questions