I have a script that pulls a date from a table. The date is extracted at 20170725. When I use the query: (if length(sr_stu_demo.DT_GRD_9_ENTRY) = 8 then ((substr(sr_stu_demo.DT_GRD_9_ENTRY,1,4))+4) else sr_stu_demo.dob endif) as cohort The value comes out as 2017.000000; however, when I use the query: (if length(sr_stu_demo.DT_GRD_9_ENTRY) = 8 then ((substr(sr_stu_demo.DT_GRD_9_ENTRY,1,4)) else sr_stu_demo.dob endif) as cohort The value is displayed as 2017. How do I fix the first statement so I have 0 decimal place? |
Please confirm "The value comes out as 2017.000000" is incorrect, and the result is actually 2021.000000 as shown below. Also please confirm that DT_GRD_9_ENTRY is a VARCHAR column, not a DATE column. If neither is true, please disregard this answer :) CREATE TABLE sr_stu_demo ( DT_GRD_9_ENTRY VARCHAR ( 100 ), dob VARCHAR ( 100 ) ); INSERT sr_stu_demo VALUES ( '20170725', '1999' ); COMMIT; SELECT (if length(sr_stu_demo.DT_GRD_9_ENTRY) = 8 then ((substr(sr_stu_demo.DT_GRD_9_ENTRY,1,4))+4) else sr_stu_demo.dob endif) as cohort FROM sr_stu_demo; cohort 2021.000000 When you add an integer to a string, as in ((substr(sr_stu_demo.DT_GRD_9_ENTRY,1,4))+4), the string is implicitly converted to a number before the addition takes place. If you don't take control over that implicit conversion, you may get a data type you don't want... in this case NUMERIC ( 30, 6 ). You can use the EXPRTYPE function to determine what the data type is going to be: SELECT EXPRTYPE ( 'SELECT ((substr(sr_stu_demo.DT_GRD_9_ENTRY,1,4))+4) FROM sr_stu_demo', 1 ); numeric(30,6) How you "take control" is up to you; here is one example using CAST ( expression AS INTEGER ): SELECT CAST ( (if length(sr_stu_demo.DT_GRD_9_ENTRY) = 8 then ((substr(sr_stu_demo.DT_GRD_9_ENTRY,1,4))+4) else sr_stu_demo.dob endif) AS INTEGER ) as cohort FROM sr_stu_demo; cohort 2021 Thanks for the answer. I appreciate it. on the statement ((substr(sr_stu_demo.DT_GRD_9_ENTRY,1,4))+4), without the +4 the data would come out - for example - as 2017. Once the +4 is added the value returned comes out as 2021.000000. Data type is 'char'; however, the value is actually a date. does that help?
(31 Jul '17, 13:53)
jameshyde1979
Replies hidden
3
does that help who? :) The +4 changes everything... it is what forces the data conversion from string to numeric because you can't do arithmetic addition between a string and a number. Also, in the view of SQL Anywhere, the value is not a date, it is a string because the data type is char. There is no AI component in SQL Anywhere, it cannot determine your intent :)
(01 Aug '17, 06:33)
Breck Carter
|