ShowCase Virtual User Group: Date Wrappers and Date Conditions

1
ShowCase Virtual User Group Date Wrappersand
Date Conditions

• May 18, 2006

2
Agenda

• Introduction to Date Functions
• Details on
• Date Wrappers
• Advanced Date Functions
• Conditions, Calculations and Expressions with
  Dates
• Demonstration
• Summary

3
Date Functions

• Date functions process date data
• Change data types to dates
• Date Wrappers
• Use date functions in conditions
• Combine date functions

4
What are Date Wrappers?

• Convert stored data into dates that can be
  interpreted and displayed on the PC
• Example
• Data stored in DDMMYY format 231106
• Date displayed in Windows format 11/23/2006
• Displayed format is based on the Windows date
  setting

5
Date Wrappers Must Match Exactly

• Consider various interpretations of the numeric
  value 100305 in field HireDate
• If this is intended to represent October 3, 2005,
  then the format of the source data is MMDDYY, and
  the correct date wrapper to use is
• DATE(HireDate,MMDDYY)
• If any of the other date wrappers are used, the
  value will be interpreted differently and
  incorrect dates will be displayed

6
Combine fields as one date field

• Three, 2-Position Values - YY, MM, DD
• CHAR fields
• Concatenate the fields to combine
• Numeric fields
• Combine using arithmetic
• DATE((MM10000)(DD100)YY),MMDDYY)

7
The DATE Function

• The syntax of the DATE function is as follows
• DATE(Date_Value, Date_Format,CHAR)
• Date_Value is the date value (data) to be
  converted
• Date_Format is the stored format of the date
• CHAR if the Date_Value is a character

8
The DATE Function

• Converts stored values to DATE data type
• Only available when using ShowCase ODBC
• Syntax
• DATE (Expression, SourceFormat, Char)
• Examples
• DATE (20060115, YYYYMMDD, Char) gt 1/15/2006
• DATE (106015, CYYDDD) gt 1/15/2006
• DATE (38731, HYF) gt 1/15/2006

9
Other Date Functions

• Date functions process date data.
• Examples
• The day of the week
• DAYOFWEEK (5/18/2006) gt 5 (Thursday)
• Todays system date
• CURDATE ( ) gt 5/18/2006
• The number of days from the beginning of the
  first century (January 1, 0001)
• DAYS (CURDATE()) gt 732449

10
Performing Calculations with Dates

• Date functions can be combined
• Examples
• Elapsed time between two dates
• DAYS (3/15/2006) DAYS (2/1/2006) gt 42
• Todays system date plus 45 days
• CURDATE() 45 DAYS gt 4/29/2006 (when run on
  3/15/2006)
• Two months from a date
• DATE (1/15/2006) 2 MONTHS gt 3/15/2006
• One year ago
• CURDATE () - 1 YEAR gt 3/15/2005 (when run on
  3/15/2006)

11
Expressions Using Dates

• Aging Example
• Calculate the number of days between two given
  dates
• New Column
• DAYS(CURDATE()) - DAYS(DATE(Date_Value,Date_For
  mat))
• Range Example
• Find dates in a defined range
• Condition
• DATE(Date of Invoice,YYMMDD) BETWEEN
• (CURDATE() - 10 DAYS) AND (CURDATE() 20
  DAYS)

12
Demonstration

• Lets go to Query and see the Date Functions

13
Summary

• Use functions and date wrappers to convert other
  data types to actual dates
• Use converted dates for date calculations and
  comparisons
• Simplify and automate conditions (current date,
  date ranges)
• Combine date functions for YTD calculations

14
Questions and Discussion
15
www.spss.com/showcaseusers/
16
Thank you for joining us today!Be sure to join
our next meeting July 20Customer Speaker from
Manheim Auctions Complex Cubes and Calculations
for a Complex Organization