Example: Selecting records based on the current date

This example illustrates how to select records based on the current date in the numeric or character field.

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.

Create Table TestDate (
  PKCol    Int             Primary Key,
  DecDate  Decimal( 9,0 ),
  CharDate Char( 8 ) )
  
Insert Into TestDate Values ( 1, 20010711, '20010711' )

Use this SQL statement to compare against the numeric field:

Select  *                                                         
  From  TestDate                                         
  Where DecDate =                                                
        100 * ( 100 * Year( CurDate() ) + Month( CurDate() ) ) + 
        Day( CurDate() )

Using a Cast expression, you can convert this 8-digit number to a character value, as in the following example:

Select  *                                                         
  From  TestDate                                         
  Where CharDate = Cast(                                         
        100 * ( 100 * Year( CurDate() ) + Month( CurDate() ) ) + 
        Day( CurDate() ) As Char( 8 ) )

Use care when converting a Month() or Day() return value to a character with Cast. If you do not explicitly handle values less than 10, there might be spaces instead of zeros in the result.