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)