Retain date time format from SAS to SQL

[Babloo]

I've the variable which holds the date time value as 18.09.2019 12:49  in SAS. I've created this datetime value in SAS with the help of user defined Format.

However If I load this data in the table which was in SQL Server by using proc append force, I could see the value as

2019-09-18 12:49:56.000 which is different from the Format which was in SAS.

Could you please help me understand how to make SQL table to display the value as it was in SAS without any change in Format?

[utrocketeng]

if the value is stored in the database as a datetime, then you may be able to use a formatting option in your sql code.  here is an example for MSSQLServer:

SELECT FORMAT (getdate(), 'dd.MM.yyyy hh:mm') as time

result:
'18.09.2019 08:15'

[Babloo]

I'm asking for the SAS code or some check to preserve the SAS format in SQL
table

Yes, it's a date time column in SQL table

[Tom]

Sounds like a question for an SQL Server forum since you are asking about how to control the way that SQL Server displays datetime values.

[Babloo]

I thought it's a question to SAS community as it is the SAS data which is
being loaded in SQL server table.

[Tom]

You might have luck since I am sure many SAS users also use SQL Server.

After a quick Google search it does not look like SQL Server has anyway to set the DISPLAY format for datetime values.  That seems to be controlled by your language settings. 

I did find the SET DATEFORMAT command.  But it appears to only change the default INPUT format for datetime values in your current SQL Server session. That is how it converts character strings into datefime values, not how it display datetime values.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql?view=sql-server-20...

[Kurt_Bremser]

You're no longer dealing with SAS when you load the data into a foreign database. Once the data is there, you have to use the tools and syntax of the DBMS. SAS can only make sure that the raw values are converted correctly, and use the DBMS's format that fits best. Expecting your custom format to automagically work in SQL Server is blue-eyed at best.

---

@Babloo wrote:
I thought it's a question to SAS community as it is the SAS data which is
being loaded in SQL server table.

---

[ErikLund_Jensen]

Hi @Babloo 

Your datetime-value contains seconds, which is proved when the value is loaded into SQL, where it is displayed with seconds. And - as pointed out by other contributors - it is a SQL problem to suppress the display of seconds, when the actual value contains seconds.

In SAS, you use a format which displays only minutes, but this doesn't change the value of the variable, only the way it is displayed. 

But if you change the value in SAS by removing the seconds after last completed minute before load, the value is loaded without seconds, so 2019-09-18 12:49:56.000 is displayed as 2019-09-18 12:49:00.000 in SQL. It is still displayed the same way in SAS.

Then the same value is displayed i SAS and SQL, only padded in SQL with zeroes for seconds and miliseconds. I don't know if this will solve your problem, but it is the only way I can imagine to get rid of the seconds in SAS before load.

You can remove the seconds with this expression: dtvalue = floor(dtvalue/60)*60;