如何在表格中插入特定日期格式？(How can I insert specific date formats into a table?)

我在表中有一些varchar（8）字符串，看起来像这样。 20180630 20180331 20180630

如果我选择所有，像这样，他们看起来很好。

Select Left(StartDate,4)+'-' + Substring(StartDate,5,2)+ '-'+Right(StartDate,2), Left(Expiry_Date,4)+'-' + Substring(Expiry_Date,5,2)+ '-'+Right(Expiry_Date,2) From MyTable

问题是我试图将记录从一个表插入到另一个表中，就像这样。

Insert Into TBL_TRANS_FINAL(CURRENCY, AMOUNT, Left(StartDate,4)+'-' + Substring(StartDate,5,2)+ '-'+Right(StartDate,2) as StartDate, EFFECTIVESTARTDATE, Left(Expiry_Date,4)+'-' + Substring(Expiry_Date,5,2)+ '-'+Right(Expiry_Date,2) as Expiry_Date) ... select * from TMP3 where IS_SPECIAL_DATE <> 'N' and AsofDate <> '' ;

我一直收到错误： Incorrect syntax near the keyword 'Left'.

我认为这应该很简单。我在这里想念的是什么？

I have some varchar(8) strings in a table that look like this. 20180630 20180331 20180630

If I select all, like this, they look fine.

Select Left(StartDate,4)+'-' + Substring(StartDate,5,2)+ '-'+Right(StartDate,2), Left(Expiry_Date,4)+'-' + Substring(Expiry_Date,5,2)+ '-'+Right(Expiry_Date,2) From MyTable

The problem is that I am trying to insert records form one table into another, like this.

Insert Into TBL_TRANS_FINAL(CURRENCY, AMOUNT, Left(StartDate,4)+'-' + Substring(StartDate,5,2)+ '-'+Right(StartDate,2) as StartDate, EFFECTIVESTARTDATE, Left(Expiry_Date,4)+'-' + Substring(Expiry_Date,5,2)+ '-'+Right(Expiry_Date,2) as Expiry_Date) ... select * from TMP3 where IS_SPECIAL_DATE <> 'N' and AsofDate <> '' ;

I keep getting an error about: Incorrect syntax near the keyword 'Left'.

This should be pretty simple, I think. What am I missing here?

最满意答案

您需要使用INSERT INTO tab(col_names) SELECT ... FROM ...语法：

Insert Into TBL_TRANS_FINAL( col_name1, --... col_name5 ) select CURRENCY, AMOUNT, CAST(StartDate AS DATE), EFFECTIVESTARTDATE, CAST(Expiry_Date AS DATE) from TMP3 where IS_SPECIAL_DATE <> 'N' and AsofDate <> '';

我还会将日期存储为DATE类型。

'YYYYMMDD' date literal: Left(StartDate,4)+'-' + Substring(StartDate,5,2)+ '-'+Right(StartDate,2) <=> CAST(StartDate AS DATE)

You need to use INSERT INTO tab(col_names) SELECT ... FROM ... syntax:

Insert Into TBL_TRANS_FINAL( col_name1, --... col_name5 ) select CURRENCY, AMOUNT, CAST(StartDate AS DATE), EFFECTIVESTARTDATE, CAST(Expiry_Date AS DATE) from TMP3 where IS_SPECIAL_DATE <> 'N' and AsofDate <> '';

I would also store date as DATE type.

'YYYYMMDD' date literal: Left(StartDate,4)+'-' + Substring(StartDate,5,2)+ '-'+Right(StartDate,2) <=> CAST(StartDate AS DATE)

更多推荐