topic formatting dates for a sql server pass through in SAS Programming

formatting dates for a sql server pass through

DanD999 — Mon, 02 Nov 2020 16:58:05 GMT

I pulled this date from sql server and tried putting single quotes around it but that didn't work. I saw somewhere that %bquote was used so I tried that too. Any suggestions appreciated.

proc sql inobs=7;

connect to SQLSVR (user=&user. Password=&pass. dsn=&db.);

create table members as

select * from connection to SQLSVR

(

select *

from jkl.table1

where entered_dt >= '22OCT2018:07:00:00.0000000'

);

disconnect from sqlsvr;

quit;

Thanks

Re: formatting dates for a sql server pass through

RichardDeVen — Mon, 02 Nov 2020 18:23:40 GMT

Try

YYYY-MM-DD

where entered_dt >= '2018-11-22'

Re: formatting dates for a sql server pass through

DanD999 — Mon, 02 Nov 2020 19:06:33 GMT

@RichardDeVen wrote:

Try
YYYY-MM-DD
So
where entered_dt >= '2018-11-22'

The query runs successfully but it doesn't give the correct answer. How do I find rows where the time is after 7AM on that date?

Thanks

Re: formatting dates for a sql server pass through

DanD999 — Mon, 02 Nov 2020 19:09:21 GMT

@RichardDeVen wrote:

Try
YYYY-MM-DD
So
where entered_dt >= '2018-11-22'

If I change the datetime to '2020-10-22:07:00:00.0000000' I get this error:

ERROR: CLI open cursor error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Conversion failed when converting

date and/or time from character string.

Re: formatting dates for a sql server pass through

SASKiwi — Mon, 02 Nov 2020 19:27:01 GMT

A Google search will provide the answer:

 '2020-10-22 07:00:00'

Re: formatting dates for a sql server pass through

DanD999 — Mon, 02 Nov 2020 19:36:18 GMT

@RichardDeVen wrote:

Try
YYYY-MM-DD
So
where entered_dt >= '2018-11-22'

This works '2020-11-22 07:00:00'. Thanks for getting me on the right track.

Re: formatting dates for a sql server pass through

SASKiwi — Mon, 02 Nov 2020 19:40:20 GMT

@DanD999 - Please mark the post as answered then.