BookmarkSubscribeRSS Feed
Anotherdream
Quartz | Level 8

Hello all. This is a very general question but I still have not been able to find the solution online.

Basically whenever I am insert dates into sql, I have to convert the dates into datetimes and have the values of 00:00:00 attached.

My question is, if you have a sql table with a column of "Date" format, how do you get SAS dates to insert into this field? When I try an insert option, sas throws the error "column X is not of the correct datatype". When I try to create a table, SAS converts the dates into 1960:03:00:93 (converting the date into seconds from 1960-01-01.

Any help would be greatly appreciated!

Thanks!

Brandon

12 REPLIES 12
ballardw
Super User

I can't help with this but you might want to describe exactly how you are connecting to the database and examples of code. Others with more experience will have more information to diagnose and help with a more concrete example of what you have attempted.

Reeza
Super User

Anotherdream wrote:

Basically whenever I am insert dates into sql, I have to convert the dates into datetimes and have the values of 00:00:00 attached.

I think that's the answer there. Or do you need to know how to do that conversion?

You could write a macro that converts all date variables to datetime variables with 00:00:00.

esjackso
Quartz | Level 8

I dont think SQL Server added a date only data type until 2008 and then developers would have to use that for date only fields. Previous datetime I believe was the only storage option (of course the view could be formated to show what every the developer wanted). Since you are having this issue I imagine that your solution, like suggested, is to add the time to the dates you have.

I have done this in the past as part of the insert sql, but you could do it in a datastep prior to the insert as well.

EJ

Anotherdream
Quartz | Level 8

I understand that the data type was only added in 2008, however this seems like a fundamental short-sight of being able to transfer data between two sources. EXAMPLE: What if a sql table has a field that is specified as a "date" format. You cannot then take ANY sas data and get data into this field and have it mapped correctly as a date. If you format it as a standard Date, or character, or Datetime, sas throws the errror (Type of column 2 not recognized).

Here is some example code that creates such a table and tries to insert data into it.

SQL SERVER CREATE TABLE STATEMENT

Create table dates_test(

     [KEY]     NUMERIC     null,

     [Datefield]     [date]     null)

     on [primary]

go

Sas code to insert into this table

data person;

   infile datalines delimiter=',';

   informat key best32.;

   informat datevalue mmddyy10.;

   format key best32.;

   format datevalue mmddyy10.;

   input key  datevalue ;

   datalines;                     

1,01/01/1999

2,02/27/2009

;

libname CstOrder odbc dsn='odbc_my_odbcname' schema=dbo bulkload=yes DBMAX_TEXT=32000 ;


proc sql;
insert into cstorder.DATES_TEST

select * from person;
quit;
run;

Is there no way to get the date field from SAS into a corresponding date field in an already created sql table? (basically this would imply we cannot use the date field datatype for any tables and have it communicate with sas, which again seems like a massive defect).

Thanks!

Brandon

esjackso
Quartz | Level 8

My appoligies I think I misunderstood your issue. Is it that you have dates in SAS and are trying to use the actual date datatype in SQL server? if that ist the case I have not dealt with that as most of our SQL Server developers just use the historical datetime field.

This may be better addressed with SAS Tech Support, I would assume that the SAS date field should correctly convert to the SQL server date field.

EJ

Anotherdream
Quartz | Level 8


Hello esjackso1. That is correct, you have perfectly stated my problem. I am able to get datetimes into sql server, but I cannot get an actual DATE into a field of the datatype DATE, which is a problem because quite a few of our tables are using the "DATE" format (in sql).

I will open a ticket with sas tech support, however I am curious if anyone at all has run into this problem before?

Thanks all!

Anotherdream
Quartz | Level 8

Hello all. I am sorry for the inconvience, I have just discovered how to solve this problem.

It turns out, if you specify the SAS data as a charater value, in the format MMDDYY10., then insert it into the sql table (with the DATE Type) sql server actually convers the character value into a "DATE" field.. (very strange I think).

Thanks all for your time!

Brandon

esjackso
Quartz | Level 8

Good to know ... Thanks for posting your solution!

EJ

LinusH
Tourmaline | Level 20

Doesn't sound like a best practice to me...

Have you tried DBSASTYPE?

Data never sleeps
Tom
Super User Tom
Super User

Is that for when specifying the date as a literal value? or when inserting from a SAS dataset?

LinusH
Tourmaline | Level 20

Can't see any use when specifying constants, since you have the possibility to use the appropriate format when specifying the constant. So it's for column values, and it works both ways (read/write). For inserting, see example 3:

SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

Data never sleeps
RosieT
Calcite | Level 5

Wonder if there's any chance of reviving this as I have a question - I tried using DBSASTYPE in the following context:

data SQLlib.TasksNotRun (DBSASTYPE=(LastRunSuccess='DATE'));
set TaskList;
where LastRunSuccess lt LastExpectedRun;
run;

 

This uses an ODBC connection for the 'SQLlib' libname and I'm using SQL Server 2014 for my databases.

 

The input data column, from the table 'TaskList', is in YYMMDD10. format. However the table it creates in SQL has a datetime format for this column.

Not a disaster for what I'm using it for, but I wanted my output to look nicer...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 12903 views
  • 0 likes
  • 7 in conversation