Replace function SQL-CodePudding

I have problem that replace function does not work

DECLARE @Tabela nvarchar(25)
DECLARE @query nvarchar(max)

SET @Tabela = '_#tmp_tt2_POS_racuni_'
SET @query = 'SELECT * INTO ' @Tabela ((replace(convert(varchar(10), getdate(),121),'''-''','''')) '-' (replace(convert(nvarchar(10),getdate(),108),''':''',''''))) 'NP' ' FROM _tabels'

PRINT @query

SELECT * 
INTO _#tmp_tt2_POS_racuni_2021-12-21-11:15:27NP 
FROM _tabels 

Completion time: 2021-12-21T11:15:27.0724917 01:00

CodePudding user response：

The quotes you use are two too many.

You are using replace(date,''':''',''''). This will replace ':' with ''. However, the getdate() doesn't have quotes itself. I guess you did that because of the dynamic sql you are using - but for the dates, you should omit the quotes:

replace(date,':','')

CodePudding user response：

You should use FORMAT and specify the format you want directly instead of going through intermediate formats. For example :

select format(getdate(),'yyyyMMddhhmmss')

Produces 20211221124017. FORMAT is slower than CONVERT but in this case it's only called once. It's far more important to write a readable query that produces the correct result.

That said, it's probably better to use table partitioning instead of creating lots of temporary tables with a date in the name. All supported SQL Server versions and editions support partitioning, even LocalDB

CodePudding user response：

Firstly, let's get onto the real problem that is discussed at lengths in the comments; this is a terrible idea.

The fact you want to create a table for an exact point in time smells very strongly of an XY Problem. What is the real problem you are trying to solve with this? Most likely what you really want is a partitioned table or a temporal table, so that you can query the data for an exact point in time. Which you need, we don't know, but I would suggest that you rethink your "solution" here.

As for the problem, it's working exactly as intended. Let's look at your REPLACE in solitude:

replace(convert(varchar(10), getdate(),121),'''-''','''')

So, in the above, you want to replace '-' (a hyphen wrapped in single quotes) with '' (2 single quotes). You don't want to replace a hyphen (-) with a zero length string; that would be REPLACE(..., '-','').

The style you are using, 121 gives the format yyyy-mm-dd hh:mi:ss.mmm, which doesn't contain a single single quote ('), so no wonder it isn't finding the pattern.

Though you don't need REPLACE on that date at all. YOu are taking the first 10 characters or the style and then removing the hyphens (-) to get yyyyMMdd, but there is already a style for that; style 112.

The above could be rewritten as:

DECLARE @Tabela sysname;
DECLARE @query nvarchar(max);
SET @Tabela = N'_#tmp_tt2_POS_racuni_';
SET @query = N'SELECT * INTO dbo.' QUOTENAME(CONCAT(@Tabela,CONVERT(nvarchar(8),GETDATE(),112),,N'-'.REPLACE(CONVERT(nvarchar(10),GETDATE(),108),':',''),N'',N'NP') N' FROM dbo._tabels;'
PRINT @query;