on 2017 Jul 29 9:18 PM
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?
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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 🙂
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.