Date Field(yyyy-mm-dd). Remove Day from Date Field

Database
Enthusiast

Date Field(yyyy-mm-dd). Remove Day from Date Field

I need to remove the day part of a date field (yyyy-mm-dd).  I tried the following statement:

SELECT EXTRACT(DAY FROM acct_cre_dt) AS VintageDay,

Thanks for your help in advance!

j1eggert

8 REPLIES
Enthusiast

Re: Date Field(yyyy-mm-dd). Remove Day from Date Field

The extract function is used to get a specific part of a date, e.g. day, month or year. To remove the day part you can use the following query:

SELECT CURRENT_DATE (FORMAT 'YYYY-MM');

Unfortunately ODBc do not support this formatting and  SQLA will show the date in your system specified format. 

But it works fine in BTEQ.

Khurram
Enthusiast

Re: Date Field(yyyy-mm-dd). Remove Day from Date Field

This is the first part of my create table qry:

Create table pp_oap_jeggert_t.ACH_US_MA_VintageData_V1 AS (

SELECT C.acct_cre_dt (FORMAT 'yyyy-mm'),

How do I include the field name into the statement you provided to only show yyyy-mm?

Enthusiast

Re: Date Field(yyyy-mm-dd). Remove Day from Date Field

You can use either the keyword AS to name a column or by providing a list of columns in the table header.

for example

Create table pp_oap_jeggert_t.ACH_US_MA_VintageData_V1 AS (
SELECT C.acct_cre_dt (FORMAT 'yyyy-mm') AS SHORT_DATE,

OR

Create table pp_oap_jeggert_t.ACH_US_MA_VintageData_V1(SHORT_DATE,...Other column names) AS (
SELECT C.acct_cre_dt (FORMAT 'yyyy-mm'),

The naming position should be according to the columns in the select list.

Khurram
Enthusiast

Re: Date Field(yyyy-mm-dd). Remove Day from Date Field

I used the first option and created the table.  Unfortunately, it did not change the format of the date field.

Enthusiast

Re: Date Field(yyyy-mm-dd). Remove Day from Date Field

Sorry, to add the column attribute like format you need to use the following syntax:


CREATE TABLE Test(sdate FORMAT 'YYYY-MM')

AS

(SELECT date_col FROM TableName)WITH DATA;

You can use the show table command to confirm the colum format

SHOW TABLE Test;

As I have explained above, in SQLA when you select the date column it displays date in you machine format.

If you want to see the real change, use BTEQ.

Khurram
Junior Contributor

Re: Date Field(yyyy-mm-dd). Remove Day from Date Field

There's no way to store only YYYY-MM in a DATE column, when you add FORMAT 'YYYY-MM' it's just for a typecast to a string, but the day is still included.

When you only need it for output use CAST(datecol (FORMAT 'YYYY-MM') AS CHAR(7)) to get a '2013-09' as a string or datecol / 100 + 190000 to get 201309 as integer.

Dieter

Enthusiast

Re: Date Field(yyyy-mm-dd). Remove Day from Date Field

Thanks, this is very helpful!

Enthusiast

Re: Date Field(yyyy-mm-dd). Remove Day from Date Field

Thanks diether for the clarification!

Khurram