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
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
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,':','')
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;
_#tmp_tt2_POS_racuni_isn't going to be temporary like it's name implies either. A temporary table's name must begin with a has (#); yours starts with an underscore (_). It seems both (read all?) your table (object) names do. I hope not.REPLACEfunction is working, there's just nothing to replace. Style121returns avarcharin the formatyyyy-mm-dd hh:mi:ss.mmm, and you want to replace the value'-'with''. There are no'-'patterns in your string. You actually should be replacing just-with a zero length string.112(yyyymmdd); then you don't need to use the first 10 characters of style121(yyyy-mm-dd hh:mi:ss.mmm) and remove the hyphens (-).