Pass Datetime in SQL query in string format?

I have string query in and pass @Date object to string. It is giving error. See below code.

Declare @MidDate datetime, @MaxDate datetime


set @qrysales_trans_unit_26wks ='update historical_result
    set sales_trans_unit_26wks = (      
            SELECT      
            SUM(sales_trans_unit)
            FROM r开发者_StackOverfloweg_summary_rowno  WHERE  
            period_idx >= '+  @MidDate  // error 
            +' AND period_idx <'+  @MaxDate /error
            +' AND Client_id ='+ @Client_id
            +' and historical_result.[store_idx] = reg_summary_rowno.[store_idx]
            And [attributes] ='+  @attributes +')'

How to pass Datetime object in the proper way to string Query?

---

Try using two single quotes to escape quote marks so dates end up like: period_idx >= '@MidDate'

set @qrysales_trans_unit_26wks ='update historical_result
        set sales_trans_unit_26wks = (      
                SELECT      
                SUM(sales_trans_unit)
                FROM reg_summary_rowno  WHERE  
                period_idx >= '''+  @MidDate
                +''' AND period_idx <'''+  @MaxDate
                +''' AND Client_id ='+ @Client_id
                +' and historical_result.[store_idx] = reg_summary_rowno.[store_idx]
                And [attributes] ='+  @attributes +')'

Click here for more information on escaping quotes in SQL.

---

A couple of better options, IMHO.

If you really want to use dynamic SQL, read up on sp_executesql - and use the ability to pass in parameters to the SQL. You'll prevent SQL injection attacks this way and will also avoid running into problems with having to string-ify parameter values.

Otherwise, used stored procedures - which I would consider the better option here.

---

To fix your ERROR, you need to add some single quotes ' around the dates within the string.

One more thing which improves clarity. Use the BETWEEN keyword:

    WHERE period_idx BETWEEN @MinimumDate AND @MaximumDate

---

You can use instead of datetime a smalldatetime And you may use the dates like this :

Declare @MidDate smalldatetime,
set @MidDate = '20110317'

Hope it helps.

---

If you must pass a date in string format - first of all, put it in quotes, and second of all, I would strongly urge you to use the standard ISO-8601 date format (YYYYMMDD or YYYY-MM-DDTHH:MM:SS).

The big benefit of these ISO standard formats is that they'll work no matter what language and regional settings your SQL Server is set to. Any other string representation is language-dependent, e.g.

05/10/2010

will mean:

• 10th of May 2010 in the US
• 5th of October 2010 in pretty much all of the rest of the world

but 20101005 is clear and never ambiguous - it's always the 5th of October, 2010 - even for in the US :-)

---

I think you should use convert before concatenate the date variable with the sentence

Declare @MidDate datetime, @MaxDate datetime
set @qrysales_trans_unit_26wks = 'update historical_result
     set sales_trans_unit_26wks = (      
            SELECT      
            SUM(sales_trans_unit)
            FROM reg_summary_rowno  
            WHERE  
            period_idx >= '+ '''' + convert(varchar, @MidDate, 112) + ''''  // error 
            +' AND period_idx <'+  '''' + convert(varchar, @MaxDate, 112) + '''' /error
            +' AND Client_id ='+ @Client_id
            +' and historical_result.[store_idx] = reg_summary_rowno.[store_idx]
            And [attributes] ='+  @attributes +')'

---

I would really recommend that you shy from concatenating SQL this way. It will really open you to injection attacks etc.

Look at this sample to see another approach that you might take.

use tempdb
create table foo (id int not null identity, data datetime)

insert foo(data) values
('1/1/2010'),('1/10/2010'),('3/31/2010')


Declare @SQLStr nvarchar(4000)

set @SQLStr = 'select * from foo where data = @Data'

exec sp_executeSQL @SQLStr, N'@Data datetime', '1/1/2010'