DB2 common functions-Date and Time Functions

Date function

Syntax: Date (ARG)

The date function returns a date in a date, timestamp, date string, or timestamp string.

SQL Code

1. Eg:
   
2. Select
    
   Date
   (
   '2017-06-12 16.24.18.121212'
   )
   From
   Test
   

 
Eg: Select date ('2017-06-12 16.24.18.121212 ') from test
 

Time Function

Syntax: Time (ARG)

The time function returns the time of a time, timestamp, or timestamp string parameter.

SQL code

1. Eg:
   
2. Select
    
   Time
   (
   '2017-06-12 16.24.18.121212'
   )
   From
   Test
   

 
Eg: Select time ('2017-06-12 16.24.18.121212 ') from test
 

Timestamp Function

Syntax: Timestamp (expres1 [, expres2])

The timestamp function returns the timestamp of one or two parameters.

SQL code

1. Eg:
   
2. Select
    
   Timestamp
   (
   '2017-06-12 16.24.18.121212'
   )
   From
   Test
   

 
Eg: Select timestamp ('2017-06-12 16.24.18.121212 ') from test
 

Note: if there is only one parameter, this parameter must be a timestamp or a timestamp string. If there are two parameters, the first parameter must be a date or date string, the second parameter must be a time or timestamp string.

Year Function

Syntax: Year (ARG)

The year function returns the year of a date, timestamp, date string, and timestamp string.

SQL code

1. Eg:
   
2. Select
    
   Year
   (
   '2017-06-12'
   )
   From
   Test
   

 
Eg: Select Year ('1970-06-12 ') from test
 

Month Function

Syntax: Month (ARG)

The month function returns the month portion of a date, timestamp, date string, and timestamp string.

SQL code

1. Eg:
   
2. Select
    
   Month
   (
   '2017-06-12'
   )
   From
   Test
   

 
Eg: select month ('2014-06-12 ') from test
 

Quarter Functions

Syntax: Quarter (ARG)

The quarter function returns the quarter of a date, timestamp, date string, and timestamp string.

SQL code

1. Eg:
   
2. Select
   Quarter (
   Current
    
   Date
   )
   From
   Test
   

 
Eg: Select quarter (current date) from test
 

Week Function

Syntax: Week (ARG)

The week function returns the week part of a date, timestamp, date string, and timestamp string.

SQL code

1. Eg:
   
2. Select
   Week (
   Current
    
   Date
   )
   From
   Test
   

 
Eg: Select Week (current date) from test
 

Day function

Syntax: Day (ARG)

The day function returns the day part of a date, timestamp, date string, and timestamp string.

SQL code

1. Eg:
   
2. Select
    
   Day
   (
   Current
    
   Date
   )
   From
   Test
   

 
Eg: Select Day (current date) from test
 

Hour Function

Syntax: hour (ARG)

The hour function returns the hour part of a date, timestamp, date string, and timestamp string.

SQL code

1. Eg:
   
2. Select
    
   Hour
   (
   Current
    
   Time
   )
   From
   Test
   

 
Eg: Select hour (current time) from test
 

Minute Function

Syntax: minute (ARG)

The minute function returns the minute part of a date, timestamp, date string, and timestamp string.

SQL code

1. Eg:
   
2. Select
    
   Minute
   (
   Current
    
   Time
   )
   From
   Test
   

 
Eg: Select minute (current time) from test
 

Second Function

Syntax: Second (ARG)

The second function returns the second part of a date, timestamp, date string, and timestamp string.

SQL code

1. Eg:
   
2. Select
    
   Second
   (
   Current
    
   Time
   )
   From
   Test
   

 
Eg: Select second (current time) from test
 

Days Functions

Syntax: days (ARG)

The days function returns a date integer representation of the date, timestamp, date string, timestamp string, and number of days since-01-01.

SQL code

1. Eg:
   
2. Select
   Days (
   Current
    
   Time
   )
   From
   Test
   

 
Eg: Select days (current time) from test
 

Dayofyear Function

Syntax: dayofyear (ARG)

The dayofyear function returns the day value of a date, timestamp, date string, and timestamp string within the year.

SQL code

1. Eg:
   
2. Select
   Dayofyear (
   Current
    
   Date
   )
   From
   Test
   

 
Eg: Select dayofyear (current date) from test
 

Dayofweek Function

Syntax: dayofweek (ARG)

The dayofweek function returns the day value of a date, timestamp, date string, and timestamp string within the week (from Sunday 1 to 7 ).

SQL code

1. Eg:
   
2. Select
   Dayofweek (
   Current
    
   Date
   )
   From
   Test
   

 
Eg: Select dayofweek (current date) from test
 

Dayofweek_iso Function

Syntax: dayofweek_iso (ARG)

The dayofweek_iso function returns the day value of a date, timestamp, date string, and timestamp string within a week (from Monday to 7 ).

SQL code

1. Eg:
   
2. Select
   Dayofweek_iso (
   Current
    
   Date
   )
   From
   Test
   

 
Eg: Select dayofweek_iso (current date) from test
 

Dayname Function

Syntax: dayname (ARG)

The dayname function returns a date, timestamp, date string, and timestamp string of the day of the week.

SQL code

1. Eg:
   
2. Select
   Dayname (
   Current
    
   Date
   )
   From
   Test
   

 
Eg: Select dayname (current date) from test
 

Monthname Function

Syntax: monthname (ARG)

The monthname function returns the month name of a date, timestamp, date string, and timestamp string.

SQL code

1. Eg:
   
2. Select
   Monthname (
   Current
    
   Date
   )
   From
   Test
   

 
Eg: Select monthname (current date) from test
 

Midnight_seconds Function

Syntax: midnight_seconds (ARG)

The midnight_seconds function returns the number of seconds between midnight and the specified time value of the parameter, which is expressed by an integer ranging from 0 to 86400.

SQL code

1. Eg:
   
2. Select
   Midnight_seconds (
   Current
    
   Time
   )
   From
   Test
   

 
Eg: Select midnight_seconds (current time) from test
 

Address: http://jacki6.javaeye.com/blog/512439