proc sql datetime column returns 0

[DorukK]

AuditInformation_CreatedDate
2021-11-15 08:35:04.4351830
2021-11-15 08:35:04.4351830
2021-12-17 06:21:12.1300279
2021-09-26 16:54:14.0000000
2021-09-14 18:39:24.0000000
2021-09-06 12:54:52.0000000
2021-09-04 19:04:44.0000000
2021-09-04 18:33:52.0000000
2021-09-15 20:42:22.0000000
2021-10-02 17:30:20.0000000
2021-09-21 18:23:30.0000000

This is my datetime column in mssql. I would like to access AuditInformation_CreatedDate column in my customer table from EG and when I select the output looks like this:

proc sql outobs=22;

select put(AuditInformation_CreatedDate, 27.) from &sas_db_libref_idmap..Customer;
quit;

SAS Output

0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

proc sql outobs=22;

select AuditInformation_CreatedDate from &sas_db_libref_idmap..Customer;
quit;
SAS Output
AuditInformation_CreatedDate
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000
01JAN1960:00:00:00.0000000

How can I solve this issue? Why does EG get my date column as 0?

[PaigeMiller]

If these values in the database are truly numeric date/time values, you shouldn't need to do any PUT or INPUT, you just need the proper format.

select AuditInformation_CreatedDate format=datetime24. from &sas_db_libref_idmap..Customer;

But if they are character values, that won't work. So, are they numeric or are they character? What does PROC CONTENTS say about this variable in &sas_db_libref_idmap..customer?

[DorukK]

Here is the script and output for proc contents:

proc sql outobs=22;
create table _Temp_ as
select AuditInformation_CreatedDate from &sas_db_libref_idmap..Customer;
quit;

proc contents data=work._temp_;
run;

This is the data type in the database:

[PaigeMiller]

So does the solution I gave help?

You shouldn't even need to apply a format here, as it is already formatted.

[DorukK]

In the database the values are not 01JAN1960. When we set the format as datetime24. it returns 01JAN1960

[PaigeMiller]

You said the values in the database are, for example

2021-11-15 08:35:04.4351830

Is that correct? So if you do a PROC PRINT directly from the database, what do you see?

proc print data=&sas_db_libref_idmap..Customer(obs=10);
    var AuditInformation_CreatedDate;
run;

[DorukK]

Yes, the values are like 2021-11-15 08:35:04.4351830 value in the database.

Here is the output for the script:

proc print data=&sas_db_libref_idmap..Customer(obs=10);
var  AuditInformation_CreatedDate;
run;

[PaigeMiller]

Ok, make a screen capture the actual database and show us the first 10 rows or so.

[DorukK]

[PaigeMiller]

Okay, this is not making sense to me. Maybe others have some idea. @Reeza  @Tom @Kurt_Bremser @ballardw @Ksharp 

[ballardw]

---

@DorukK wrote:

In the database the values are not 01JAN1960. When we set the format as datetime24. it returns 01JAN1960

---

This likely indicates that somewhere in the process you have a value extremely close to if not exactly zero.

I might suggest running this and showing the result:

proc sql outobs=22;

select put(AuditInformation_CreatedDate, best16.) from &sas_db_libref_idmap..Customer;
quit;

The 27. format would round anything between -0.5 and 0.5 to zero, so your format choice may be hiding something.

If you still see zero then that is the value sent across from your connection to the external data base. At that point I would ask how you know the values you are showing with the 01Jan1960 date portion are from the same records as the dates you show in the first post? Suggest that you consider looking at some identification variables and make sure you are comparing the same records.

I am guessing that your picture of the variable properties in the external database that has a "not null" as part of the description means that there must be something in the field. So you may just possibly find out that 0 (zero) is a default date when a more interesting value is not yet assigned such as an Audit has not been created yet. You might check to see if the data set is sorted by that value.

[Tom]

How did you connect to the database?

What ENGINE are you using? ODBC? Something else?

What driver version are you using for the connection?