Adding space in a dynamic query sql

Question

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(CONVERT(VARCHAR(20),getdate(),100),2) AS CurrentDate'
EXECUTE (@QUERY)

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

gbn · Accepted Answer · 2010-12-18 06:19:20Z

3

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...

edited Dec 18, 2010 at 6:19

answered Dec 18, 2010 at 6:14

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Flipster · Accepted Answer · 2010-12-18 06:15:25Z

2

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.

answered Dec 18, 2010 at 6:15

Flipster

4,4014 gold badges31 silver badges37 bronze badges

2 Comments

Mathew Paul Over a year ago

@FilipScript Thank you for the reply its working in sql server but when i call the same using datatable its getting null. i am simply writing it as a procedure.

Mathew Paul Over a year ago

@FilpScript Sorry it was not the code error actually my length of the varchar was the problem when i increased the length it started performing good. Thanks alot for your help.

Thomas · Accepted Answer · 2010-12-18 06:23:53Z

0

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)

answered Dec 18, 2010 at 6:23

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

1 Comment

bernd_k Over a year ago

It absoluty correct OK, but it is a bit annoying when you often switch betwenn MS-SQLServer and Oracle. The later calls the same function CHR. '' '' works for both and is more readable.

Collectives™ on Stack Overflow

Adding space in a dynamic query sql

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related