雪花时间戳转换

Question

下面的代码给出了2021年8月6日星期五12:16:27 GMT-0700（太平洋夏时制）的值，而不是mm/dd/yyyy hh：mm：ss。我在这里漏掉了什么？请告知

create or replace procedure Load_Employee()
  returns varchar not null
  language javascript
  EXECUTE AS CALLER
  

  as
  $$
  //Variable Initialization
    
    var IntegrationTable ='EMPLOYEE';
    var TypeID=0;
    var MaxDate=' ';
    var cmd = "Select max(COMPLETED_DATE) from SCHEMA.TABLE where TARGET_TABLE_NAME=  " + "'" + IntegrationTable + "'"  ;
  
    var sql = snowflake.createStatement({sqlText: cmd});
    var result = sql.execute();
    result.next();
 
   MaxDate=result.getColumnValue(1);
   return MaxDate;


  
  $$
  ;

Answer 1

这就是JavaScript以字符串形式呈现日期的方式。您可以像这样更改为YYYY-MM-DD：

create or replace procedure Load_Employee()
  returns varchar not null
  language javascript
  EXECUTE AS CALLER
  

  as
  $$
  //Variable Initialization
    
    var IntegrationTable ='EMPLOYEE';
    var TypeID=0;
    var MaxDate=' ';
    var cmd = "Select max(COMPLETED_DATE) from SCHEMA.TABLE where TARGET_TABLE_NAME=  " + "'" + IntegrationTable + "'"  ;
  
    var sql = snowflake.createStatement({sqlText: cmd});
    var result = sql.execute();
    result.next();
 
   MaxDate=formatDate(result.getColumnValue(1));
   return MaxDate;



// Helper functions

function formatDate(date) {
    var d = new Date(date),
        month = '' + (d.getMonth() + 1),
        day = '' + d.getDate(),
        hour = '' + d.getHours(),
        minute = '' + d.getMinutes(),
        second = '' + d.getSeconds(),
        year = d.getFullYear();

    if (month.length < 2) month = '0' + month;
    if (day.length < 2) day = '0' + day;
    if (hour.length < 2) hour = "0" + hour;
    if (minute.length < 2) minute = "0" + minute;
    if (second.length < 2) second = "0" + second;

    return [month, day, year].join('/') + " " + [hour,minute,second].join(':');
}
    
  
  $$
  ;