I have the below query in whcih i need to compare timestamp value 'YYYY-MM-DDBHH:MI:SS.S(6) with
a date value in format '01/23/2014 11:53:50.000000' as below , but i am getting error as invalid timestamp
CAST(SUBSTRING (CAST (A.DW_LOAD_TMP AS VARCHAR(19)) ,1,19) ) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS')>
CAST(CAST ( SUBSTRING('01/23/2014 11:53:50.000000',1,19) AS VARCHAR(19)) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS')
works for me in both BTEQ and SQL Assistant over ODBC/.NET:
SELECT '01/23/2014 11:53:50.000000' AS ts,
ts (TIMESTAMP, FORMAT 'MM/DD/YYYYBHH:MI:SS.S(6)') AS a,
CAST(SUBSTR (CAST (ts AS VARCHAR(19)) ,1,19) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS') AS b;
*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.
ts a b
-------------------------- -------------------------- -------------------
01/23/2014 11:53:50.000000 01/23/2014 11:53:50.000000 01/23/2014 11:53:50
Which client do you use?
Btw, unless you want less fractional seconds there's no need to do a SUBSTRING
Below worked for me.
Can you help me withe the below logic
END_EFF_DATE=(BEG_EFF_DT of next record)- 1 micro second.
For me the MAX (END_EFF_DT) is 31/12/9999.....thats y using the below format.
I am using the below but its throwing error:
Why don't you simply tell which error is returned?
This fits your narration:
OVER (PARTITION BY A.SUBSCBR_NUM,A.MBR_SUFFIX,A.CLAIM_NUM
ORDER BY A.MIN_BEG_EFF_DATE
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
- INTERVAL '0.000001' SECOND
Teradata doesn't seem to like the time in between month and year.
But you can utilize TO_DATE or TO_TIMESTAMP:
TO_DATE(s, 'dy mon dd hh:mi:ss YYYY')
I want to cast a timestamp value to date value using to_date funtion Teradata sql assistant or bteq, have tried below
SELECT TO_DATE ('4/25/2017 06:16:39', 'YYYY-MM-DD');
But It throws the error as below:
YYYY value must be faur digits and in the range of 1-9999
If I execute below
SELECT TO_DATE (upd_date, 'YYYY-MM-DD') from Table;
Here upd_date contains timestamp value, then it throws below error:
Function TO_DATE called with an invalid number or type of parameters.
Please let me know how to achive above with to_date function only, I don't want to use cast function.
TO_DATE is used to cast a VarChar to a date and the 2nd parameter is the format used for converting.
In your 1st query the format doesn't match the input string and in your 2nd query you already got a Timestamp.
Try CAST(upd_date AS DATE)