CODEWITHSUNDEEP
sqldatetimeteradata
Doug Fir
Doug Fir

Reputation: 21212

Trying to cast date and time in Teradata

I'm selecting from a table with a timestamp(6) data type.

I would like to get the date and time, to the minute, like so:

Currently the data look like this: 03/10/2014 21:54:47.690000

I would like to select so that the result looks like this: 03/10/2014 21:54:47

I tried this:

SELECT CAST(EVENT_GMT_TIMESTAMP AS DATE) || (EVENT_GMT_TIMESTAMP AS TIME)   AS theDate, SUM(VwNIMEventFct.DWELL_MINUTES) AS totalDwellMins
FROM RDMAVWSANDBOX.VwNIMEventFct
WHERE EXTRACT(MONTH FROM EVENT_GMT_TIMESTAMP) = 4
GROUP BY theDate

But received a message "Syntax error, expected something between the word EVENT_GMT_TIMESTAMP and the AS keyword"

Also tried concat().

Is there a better way, rather than concatenating, to select EVENT_GMT_TIMESTAMP as datetime but to the minute?

Also to the hour?

There must be a function that lets you select the output like this?

The ones I found researching the web do not appear to function on Teradata.

I also tried this:

SELECT CAST(EVENT_GMT_TIMESTAMP AS FORMAT 'YYYYMMDDHHMM')

But the output was like this:

04/18/2014 12:20:44.384000

I need it to be like this:

04/18/2014 12:20:44

Upvotes: 1

Views: 31094

Answers (2)

imran
imran

Reputation: 1

CAST(Column AS TIMESTAMP(0))

is a better way to do

Upvotes: 0

Andrew
Andrew

Reputation: 8703

I've never been able to get timestamp formatting to work quite like I want it, but this will get you the desired format:

SELECT  current_timestamp(6) AS ts,
  ( ts (FORMAT 'MM/DD/YYYY') (CHAR(10)))as DT, 
  cast (ts as time(6)) (char(8)) as tm,
   dt ||' ' ||tm as FormattedDate

Or, there's the new and improved version, compliments of dnoeth:

SELECT  current_timestamp(6) AS ts,
ts (FORMAT 'MM/DD/YYYYbhh:mi:ss') (CHAR(19))

Upvotes: 5

Related Questions