SQL Date Formatting

[vomer]

Hi there,

Just wondering how I can alter the following query to show date in the format I want.

Existing Date format: 15MAR2011:09:05:16.000000

Format I want: 15MAR2011:09:05:16

Query I am using:

proc sql;

create table data.test as

select       * from connection to odbc

(

select     ID,

             DATE AS CREATION_DATE,

from         maintable

);

quit;

[Reeza]

You need to add the format in to the date variable, you can do it in the same step or in a different step.

proc sql;

create table data.test as

select       ID, creation_date format=date20. from connection to odbc

(

select     ID,

             DATE AS CREATION_DATE,

from         maintable

);

quit;

[sassharp]

proc sql;

create table data.test as

select       * from connection to odbc

(

select     ID,

             DATE AS CREATION_DATE format=date20.,

from         maintable

);

quit;

[Reeza]

Will that work within the connection to ODBC, I thought that had to be native SQL in that section?

[Patrick]

As Reeza hints: you need native SQL code inside the pass-through section. But you can of course do the formatting on the SAS side.

The variable DATE seems to contain datetime values so you would need to use a datetime format.

proc sql;

create table data.test as

select   ID, DATE AS CREATION_DATE format=datetime21.

from connection to odbc

(

select     ID,

             DATE

from         maintable

);

quit;

[PGStats]

Or, if you don't care/want the time information in creation_date :

proc sql;
create table data.test as
select     ID
          ,datepart(IMPORTED_DATETIME) as creation_date format=date20.
from connection to ODBC
     (

          select     ID
                    ,DATE AS IMPORTED_DATETIME
          from maintable
     );
quit;

PG