Year wrapping on 2013-12-31

Consider the case when today is 2013-12-31, but the customer's birthday is on 2014-01-01 The first predicate will return 2013-01-01, which the next predicate will not match within its next 7 days range. How do I fix that? Thanks

 select c.CustomerCode AS 会员ID, c.LastName || ' ' ||  c.FirstName AS 姓名, c.MobilePhoneNumber AS 手机号码 from 
   customer  c 
   where  

  { fn  TRIM(CAST(CAST(YEAR(CURRENT_DATE) AS CHAR(4)) AS VARCHAR(4)))} || '-' ||
  { fn TRIM(CAST(CAST(MONTH(c.DOB)  AS CHAR(2)) AS VARCHAR(2))) } || '-' ||
  { fn TRIM(CAST(CAST(DAY(c.DOB) AS CHAR(2)) AS VARCHAR(2))) }
       BETWEEN cast({ fn DATE(CURRENT_DATE) } as date)   
     AND cast ({ fn  TIMESTAMPADD(SQL_TSI_DAY,  +7 ,   CURRENT_DATE) } as date)   
         group by c.CustomerCode, c.LastName, c.FirstName, c.MobilePhoneNumber";

Answers


You're assuming that the next birthday is in the current calendar year. There is no need to make such an assumption. You're also assuming that the combination of month/day exists in the current calendar year, which is not necessarily true, as a birthday might be on Feb 29.

Both of these can be easily avoided. Take the difference between YEAR(CURRENT_DATE) and YEAR(DOB). Suppose that's 18. Now add 18 years to DOB (which might change Feb 29 to a different date of the year), and you've got this year's birthday for the customer. Compare that to CURRENT_DATE. If it's earlier, the birthday has already passed, and the next birthday is actually 18+1 years after DOB. If it's later, the birthday has yet to pass, and you've already got the next birthday.

Now that you've determined the next birthday, you can take the difference between CURRENT_DATE and that.

Note that nowhere in this process do dates need to be manipulated as strings.

The precise details on how to express this in SQL vary based on the SQL dialect you're using. It's also not clear to me whether it's a good idea to write this in SQL at all. You can determine what makes most sense for your use.


Need Your Help

building test artifact using gradle build for a java project

java maven gradle

I am new to gradle build, I am currently working on a migrating maven project to gradle. In maven i can build both src jar and also test jar of the project. How do i do the same thing in gradle.