SQL Server Connectivity

[sas_td2016]

Hello Friends

I have an issue with date format.  Can you please help me with this query?

proc sql;
insert into sql.mortage
(loan_number, due_date, investor_numer, bankruptcy_flag, ltv)
values
(1000001,'01jan2016'd,200001,'N',0.8)
;
quit;

Outpout on Sql Server:

[Reeza]

You're trying to insert a date into a dateTIME field.

I suggest using DHMS to convert to a datetime and insert this instead.

Does this work for you?

proc sql;
insert into sql.mortage
(loan_number, due_date, investor_numer, bankruptcy_flag, ltv)
values
(1000001, dhms('01jan2016'd, 0, 0, 0) ,200001,'N',0.8)
;
quit;

[sas_td2016]

Thanks for a reply.  But I still I see the different date than what I entered.  Instead of 01Jan2016, it shows 1960-01-01.

[Reeza]

Weird. 

What happens if you use a date time literal instead of date. 

"27JAN2016:12:35:00"dt 

Please include your exact code, log and incorrect value as shown in your initial post. 

[sas_td2016]

Here is the actual code

proc sql;
insert into sql.mortage
(loan_number, due_date, investor_numer, bankruptcy_flag, ltv)
values
(1000001, '01jan2016'd, 200001,'N',0.8)
;
quit;

is there any formatting required for date variable?

output in sql server:

How do I get the same date as 01jan2016 in sql server?

Thanks

[kiranv_]

did you try '2016-01-01'

[sas_td2016]

yes, I tried.  But SAS doesn't take this format.  

[kiranv_]

ok. I have worked extensively with Teradata and SAS for many years. But I have started working recently sql server. Whenever I have to do Insert I use explicit pass through a connect statement. That is always easy way to go

just tried insert into sql server '2016-01-01' and also '2016-01-01 00:00:00.000', both of them worked. Please try to connect statment if possible as shown below.

proc sql;

connect to odbc(datasrc=xxx authdomain =yyyy)

execute(insert into sqlseverschema.table values(something , '2016-01-01' )by odbc

[Reeza]

Well, that isn't the code I asked you to try. If you try the same code I'm not really surprised you're going to get the same output. If you search on here, 99% of the time, this is the correct solution. It's possible you have something else going on, but without the code/log/output I may as well tell you to walk to the server and ask what's wrong. 

[sas_td2016]

Hello Friends

I still don't understand what Reeza mentioning here.  Can you please guide me on this?

My output should be same on the sql server as my sql query on the SAS.

Your help is highly appreciated.

[kiranv_]

@sas_td2016 This is an assumption, which may not be true sometimes. A similar query run on SQL Server and SAS may have different results. One reson I can think of is case sensitive. SQL server is case insensitive, whereas SAS is case sensitive. 

select * from database.dbo.mytablename

where name = 'hello'

LIBNAME SQLS ODBC DSN=’sql server’ user=sasjlb pw=pwd;

select * from SQLS.mytablename

where name ='hello'

Show us what is the code you run. what are the values you got or what is the error, someone can definetly help you.

[sas_td2016]

Thanks Kiran for your reply.

Here is the actual code

proc sql;
insert into sql.mortage
(loan_number, due_date, investor_numer, bankruptcy_flag, ltv)
values
(1000001, '01jan2016'd, 200001,'N',0.8)
;
quit;

is there any formatting required for date variable?

output in sql server:

How do I get the same date as 01jan2016 in sql server?

In SQL Server the date shows as '1960-01-01 05:40:54:000

Thanks again.

[kiranv_]

As @Reeza said earlier this is a datetime column, not a date column. Did you try using datetime value which @Reeza has provided. please let me know if that has nor worked.

[sas_td2016]

yes, I tried that too.  It didn't work.

[kiranv_]

check the below link someone with problem

1. https://communities.sas.com/t5/Base-SAS-Programming/Loading-sas-date-into-sql-server/td-p/235058.

please do the following if the above link does not work

1. As I mentioned and shown earlier, inserting into table using connect statement is easier bet.

2. Try using SASDATEFMT shown in the link, we used to use this a lot with date and datetime and I am sure if you following one of this example it will work. 

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p13xka550rm442n1gd...

3. Create a SAS table with format date9. with data yoou have and then insert.