number to DATE conversion

[SASAna]

Hi SAS USers,

I need some help with converting this below formatted complete_date to 'DATE' type to insert into database.

I tried

new_complete_date = input(put(datepart(complete_date),8.),YYMMDD10.)  it did not worked.

new_complete_datetime = input(put(datepart(complete_date),8.),DATETIME20.)

format new_complete_date YYMMDD10.  new_complete_datetime DATETIME20.;

Variable name             type      length       format             informat

Complete_date           Num  8              DATETIME20.  DATETIME20.

Thanks,

Ana

[Reeza · Accepted solution]

Post what your data looks like. 

You can't use datepart on a character variable, so break it into steps to see which functions work. 

Edit: I misread your question. Use datepart() alone, the database should understand the data. 

[SASAna]

Thanks Reeza, It worked with just datetime() and inserted appropriate date time in database.

[Patrick]

Which SAS version? Which Database and which DB version?

According to your code your source variable "complete_date" contains a SAS DateTime value. Do you want to load the DateTime value or only the Date part?

As an example for Oracle:

DATE in Oracle contains a value which corresponds to a SAS DateTime value and the SAS/Access engine will do the conversion for you when loading into Oracle. 

Because SAS doesn't have specific data types for Date and DateTime, the only way the SAS/Access engine can determine that a SAS numeric variable actually contains a Date or DateTime value is via the format applied to the variable. I've made with Oracle the experience that things don't work properly with all formats (which is a flaw) and I make always sure that I'm using Datetime20. or Date9. - you can simply use a data step Format statement or Proc Datasets Modify to change the permanent format on a variable.