0

I have a table called "Publicholidays" where in dates are stored as Varchar. My query should fetch all values from say table xxxx between the user selected dates that exclude the weekends(sat,sun), public holidays. I am new to DB2 so can anyone suggest me ideas please Note: in DB dates are stored as String.

naveena
  • 33
  • 1
  • 1
  • 5
  • Do not store dates as strings. The `DATE` data type is there for a reason. – mustaccio Mar 27 '15 at 11:45
  • Well, what have you tried so far? [This is a solved problem](http://stackoverflow.com/questions/5471524/add-business-days-to-date-in-sql-without-loops) in a number of DBs (that question deals with SQL Server, but the answers should be convertible). What does your date format look like? Does your table include the weekends? As others are pointing out, you really shouldn't be storing dates as strings, and having a calendar table will be a **huge** boon for this (and other queries). – Clockwork-Muse Mar 29 '15 at 04:20
  • Thanks.I have not designed this table and i need to do this implementation in the existing system.The date is stored as string in yyyy/mm/dd format. My publicholidays table doesn't have week ends. I have tried the below SELECT * FROM SSLR204 WHERE CRDTR2 BETWEEN '2015/03/01' AND '2015/03/20' and CRDTR2 in ( select CRDTR2 from SSLR204 WHERE dayofweek(DATE(TO_DATE(CRDTR2, 'YYYY/MM/DD'))) not in(1,7 ) and CRDTR2 BETWEEN '2015/03/01' AND '2015/03/20') and CRDTR2 NOT in (SELECT dates FROM PUBLICHOLIDAYS WHERE dates BETWEEN '2015/03/01' AND '2015/03/31') is there anyway to optimize this?pl help – naveena Mar 30 '15 at 06:53

1 Answers1

0

Mistake #1 - Storing dates as strings. Let's hope you have at least stored them YYYY-MM-DD and not MM-DD-YYYY.

Mistake #2 - Instead of a "Publicholidays" table, you need a Calendar (aka Dates or date conversion) table. It should have a record for every day along with a few flag columns BUSINESS_DAY, WEEKEND, PUBLIC_HOLIDAY. Alternatively, you could have a single DAY_TYPE column with values for business day, weekend and holiday. You'll also want to have a STRING_DATE column to make conversion between your string date and a true date easier.

Google SQL Calender table and you'll find lots of examples and discussions.

Lastly, strongly consider fixing your DB to store dates in a date column.

Charles
  • 21,637
  • 1
  • 20
  • 44
  • 1
    Actually, I wouldn't store a string version in the DB (or at least not for "public" consumption), because formatting is culture and language dependent - information the DB is rarely told about at query time. Formatting/parsing is a presentation-layer problem, and doesn't belong in the DB as such. But oh yes, do you want a calendar table... index the sucker up, and you have one of the most versatile and useful aggregating tables possible. – Clockwork-Muse Mar 29 '15 at 04:07