Getting Date into Sql Server -Not Datetime

Reply
Super Contributor
Posts: 418

Getting Date into Sql Server -Not Datetime

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

Super User
Posts: 10,552

Re: Getting Date into Sql Server -Not Datetime

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.

Super User
Posts: 17,963

Re: Getting Date into Sql Server -Not Datetime

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.

Super Contributor
Posts: 333

Re: Getting Date into Sql Server -Not Datetime

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

Super Contributor
Posts: 418

Re: Getting Date into Sql Server -Not Datetime

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

Super Contributor
Posts: 333

Re: Getting Date into Sql Server -Not Datetime

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

Super Contributor
Posts: 418

Re: Getting Date into Sql Server -Not Datetime


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!

Super Contributor
Posts: 418

Re: Getting Date into Sql Server -Not Datetime

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

Super Contributor
Posts: 333

Re: Getting Date into Sql Server -Not Datetime

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

EJ

Super User
Posts: 5,260

Re: Getting Date into Sql Server -Not Datetime

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

Have you tried DBSASTYPE?

Data never sleeps
Super User
Super User
Posts: 6,502

Re: Getting Date into Sql Server -Not Datetime

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

Super User
Posts: 5,260

Re: Getting Date into Sql Server -Not Datetime

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
Ask a Question
Discussion stats
  • 11 replies
  • 5046 views
  • 0 likes
  • 6 in conversation