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

Database

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

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

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?

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

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.

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
N/A

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

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

Thanks, this is very helpful!

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

Thanks diether for the clarification!

Khurram