Converting PostGreSQL Date time field at the import using SQL Statement

[ekodami]

Hi folks, I am quite new to the forum and hopefully could find some hints.

I am using a PostGreSQL db as data source and would like to convert the table epoch dates upfront at the import. This will be usfefull when performing my incremental refreshes.

In a nutshell, I want the field c6 named as "Last Modified Date" to return the actual date "1713667520" converted to "2024-04-21 2:45:20 AM".

 Here is a sample statement that I used and the error message :

= Value.NativeQuery(PostgreSQL.Database("xx.xxx.xx.xxx:xxxxx", "xxxxxxxxx"), "SELECT #datetime(1970, 1, 1)# + (c6/86400) AS ""Modified Date"" FROM t1111", null, [EnableFolding=true])

And here is the error message returned:

DataSource.Error: PostgreSQL: 42883: function datetime(integer, integer, integer) does not exist
Details:
DataSourceKind=PostgreSQL
DataSourcePath=10.166.33.144:48500;saputo_tp1_ar
Message=42883: function datetime(integer, integer, integer) does not exist
ErrorCode=-2147467259
NativeError=42883

Pls let me know if more details

Thank you

[lbendlin · Accepted solution]

Instead of #datetime(1970, 1, 1)# 

use DATE('1970-01-01')

[ekodami · Accepted solution]

Worked. Wonderful! Thanks.. much appreciated

[lbendlin · Accepted solution]

Instead of #datetime(1970, 1, 1)# 

use DATE('1970-01-01')