topic Re: Getting Date into Sql Server -Not Datetime in SAS Programming

Getting Date into Sql Server -Not Datetime

Anotherdream — Tue, 12 Mar 2013 15:52:22 GMT

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

Re: Getting Date into Sql Server -Not Datetime

ballardw — Tue, 12 Mar 2013 16:54:59 GMT

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.

Re: Getting Date into Sql Server -Not Datetime

Reeza — Tue, 12 Mar 2013 16:58:19 GMT

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.

Re: Getting Date into Sql Server -Not Datetime

esjackso — Tue, 12 Mar 2013 17:25:16 GMT

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.

Re: Getting Date into Sql Server -Not Datetime

Anotherdream — Tue, 12 Mar 2013 18:02:17 GMT

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]

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

Re: Getting Date into Sql Server -Not Datetime

esjackso — Tue, 12 Mar 2013 18:32:23 GMT

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.

Re: Getting Date into Sql Server -Not Datetime

Anotherdream — Tue, 12 Mar 2013 19:05:27 GMT

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!

Re: Getting Date into Sql Server -Not Datetime

Anotherdream — Tue, 12 Mar 2013 19:11:51 GMT

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

Re: Getting Date into Sql Server -Not Datetime

esjackso — Tue, 12 Mar 2013 19:16:11 GMT

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

Re: Getting Date into Sql Server -Not Datetime

LinusH — Tue, 12 Mar 2013 19:19:02 GMT

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

Have you tried DBSASTYPE?

Re: Getting Date into Sql Server -Not Datetime

Tom — Wed, 13 Mar 2013 00:06:53 GMT

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

Re: Getting Date into Sql Server -Not Datetime

LinusH — Wed, 13 Mar 2013 08:26:43 GMT

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

Re: Getting Date into Sql Server -Not Datetime

RosieT — Fri, 12 Jan 2018 16:37:48 GMT

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...