cancel
Showing results for 
Search instead for 
Did you mean: 

Scripting question about decimal places

Former Member
1,615

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?

Accepted Solutions (0)

Answers (1)

Answers (1)

Breck_Carter
Participant

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
Former Member
0 Kudos

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?

Breck_Carter
Participant

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 🙂