# Scripting question about decimal places

 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? asked 29 Jul '17, 20:18 jameshyde1979 26●1●3 accept rate: 0%

 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 ``` answered 30 Jul '17, 12:50 Breck Carter 27.2k●460●624●896 accept rate: 20% 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
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×52

question asked: 29 Jul '17, 20:18

question was seen: 220 times

last updated: 01 Aug '17, 06:36