community.borland.com

Article #25255: Using and manipulating the InterBase DATE datatype

Problem:
How do I remove or strip the time portion out of the date field

Solution:
CREATE TABLE DATE_TEST (STAMP_NUM INTEGER,
		 	STAMP_VALUE DATE,
		 	STAMP_TYPE VARCHAR(10));

INSERT INTO date_test (stamp_num, stamp_value, stamp_type) 
     VALUES  (1,'today','today');

INSERT INTO date_test (stamp_num, stamp_value, stamp_type) 
     VALUES  (2,'now','now');

INSERT INTO date_test (stamp_num, stamp_value, stamp_type) 
     VALUES  (3,'tomorrow','tomorrow');

INSERT INTO date_test (stamp_num, stamp_value, stamp_type) 
     VALUES  (4,'yesterday','yesterday');

COMMIT;

SELECT * FROM date_test;

  STAMP_NUM          STAMP_VALUE STAMP_TYPE 
=========== ==================== ========== 

          1 17-OCT-1997 00:00:00 today      
          2 17-OCT-1997 07:14:14 now        
          3 18-OCT-1997 00:00:00 tomorrow   
          4 16-OCT-1997 00:00:00 yesterday

UPDATE date_test SET stamp_value = 'today' WHERE stamp_num = 2;

COMMIT;

SELECT * FROM date_test;

  STAMP_NUM          STAMP_VALUE STAMP_TYPE 
=========== ==================== ========== 

          1 17-OCT-1997 00:00:00 today      
          2 17-OCT-1997 00:00:00 now        
          3 18-OCT-1997 00:00:00 tomorrow   
          4 16-OCT-1997 00:00:00 yesterday

UPDATE date_test SET stamp_value = 'now' WHERE stamp_num = 2;

COMMIT;

SELECT * FROM date_test;

  STAMP_NUM          STAMP_VALUE STAMP_TYPE 
=========== ==================== ========== 

          1 17-OCT-1997 00:00:00 today      
          2 17-OCT-1997 07:20:14 now        
          3 18-OCT-1997 00:00:00 tomorrow   
          4 16-OCT-1997 00:00:00 yesterday

UPDATE date_test 
SET stamp_value = CAST (CAST (stamp_value AS CHAR(11)) AS DATE);

COMMIT;

SELECT * FROM date_test;

  STAMP_NUM          STAMP_VALUE STAMP_TYPE 
=========== ==================== ========== 

          1 17-OCT-1997 00:00:00 today      
          2 17-OCT-1997 00:00:00 now        
          3 18-OCT-1997 00:00:00 tomorrow   
          4 16-OCT-1997 00:00:00 yesterday

Last Modified: 02-OCT-00