Adding space in a dynamic query sql

i am creating a dynamic query in which i need to put a space in a db field when retrieving the eg is as follows when i give single quotes its not accepting

DECLARE @QUERY VARCHAR(8000)

SET @QUERY='SELECT DATENAME(MM,getdate())+RIGHT(CONVERT(VARCHAR(20),getdate(),107),9)+SUBSTRING(CONVERT (VARCHAR(20),getdate(),100),12,6) + ' '+ RIGHT(CONVE开发者_运维知识库RT(VARCHAR(20),getdate(),100),2) AS CurrentDate'

EXECUTE (@QUERY)

the aim is to put a space in between hh:mm and AM/PM

Double up the quotes

DECLARE @QUERY VARCHAR(8000) 
SET @QUERY='SELECT DATENAME(MM,getdate())+RIGHT(CONVERT(VARCHAR(20),getdate(),107),9)+SUBSTRING(CONVERT (VARCHAR(20),getdate(),100),12,6) + '' ''+ RIGHT(CONVERT(VARCHAR(20),getdate(),100),2) AS CurrentDate' 
EXECUTE (@QUERY)

But why is this dynamic? Just run it in line

SELECT
   DATENAME(MM,getdate())+
   RIGHT(CONVERT(VARCHAR(20),getdate(),107),9)+
   SUBSTRING(CONVERT (VARCHAR(20),getdate(),100),12,6) + 
   ' '+
   RIGHT(CONVERT(VARCHAR(20),getdate(),100),2) AS CurrentDate

Or format in the client code...

Try this:

    DECLARE @QUERY VARCHAR(8000)
    SET @QUERY='SELECT DATENAME(MM,getdate())
+RIGHT(CONVERT(VARCHAR(20),getdate(),107),9)
+SUBSTRING(CONVERT (VARCHAR(20),getdate(),100),12,6) 
+ '' '' 
+ RIGHT(CONVERT(VARCHAR(20),getdate(),100),2) AS CurrentDate'
    EXECUTE (@QUERY) 

To escape the single quotes, use two of them next to each other.

Another alternative to doubling up quotes (and assuming the need for dynamic SQL is real) is to simply use Char(32)

 DECLARE @QUERY VARCHAR(8000)
    SET @QUERY='SELECT DATENAME(MM,getdate())
+RIGHT(CONVERT(VARCHAR(20),getdate(),107),9)
+SUBSTRING(CONVERT (VARCHAR(20),getdate(),100),12,6) 
+ Char(32)
+ RIGHT(CONVERT(VARCHAR(20),getdate(),100),2) AS CurrentDate'
    EXECUTE (@QUERY)