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's gravatar image

jameshyde1979
26113
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
permanent link

answered 30 Jul '17, 12:50

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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:

×69

question asked: 29 Jul '17, 20:18

question was seen: 1,379 times

last updated: 01 Aug '17, 06:36