2

Sorry asking again..i have a problem when i tried to insert datetime from field. The problem is Syntax error converting datetime from character string.i'm using SQL Server 2000. below is my code 

startDate = Trim(Request("StartDate"))
endDate = Trim(Request("EndDate"))

SELECT *
FROM tbl_Master
WHERE DoDate BETWEEN CONVERT(DATETIME, '"&startDate&"', 102)
                     AND CONVERT(DATETIME, '"&endDate&"', 102)

Really appreciate for that.Thanks

Regards -GreenCat-

Jacob
  • 77,566
  • 24
  • 149
  • 228
GreenCat
  • 167
  • 1
  • 1
  • 15

4 Answers4

2
declare @startdate as varchar(10)
declare @enddate as varchar(10)

set @startdate='10/02/2011'
set @enddate = '10/31/2011'


select * from [dbo].[Test]
where _date between Cast(@startdate AS DATETIME) and cast(@enddate as DAtetime)
paparush
  • 1,340
  • 1
  • 17
  • 25
  • this code you already set the date right.how if i get the date from '"&startDate&"'" – GreenCat Nov 01 '11 at 03:40
  • I would do the validation before calling the stored procedure and passing the parameters in to SQL. Validate the input in your application's code, then pass to SQL. Why pass crap data to SQL if you can catch it and deal with it before hand. – paparush Nov 01 '11 at 12:36
0

I believe you should check the format of your date string that is getting parsed on the request. The 102 in the Convert, tells SQL what format to attempt to process your string date in. Refer to this page (http://msdn.microsoft.com/en-us/library/ms187928.aspx) to see what format you're using.

If you're still having troubles, can you post what the value of Trim(Request("StartDate")) is?

I'm not near my development PC, but you could try format 106...

startDate = Trim(Request("StartDate"))
endDate = Trim(Request("EndDate"))

SELECT *
FROM tbl_Master
WHERE DoDate BETWEEN CONVERT(DATETIME, '"&startDate&"', 106)
                     AND CONVERT(DATETIME, '"&endDate&"', 106)
Daryl
  • 18,592
  • 9
  • 78
  • 145
0

you can try this

declare @startDate datetime
declare @endDate  datetime

set @startDate = value of startdate

set @endDate = value of end date

SELECT * FROM a WHERE a.Entry_Date BETWEEN CONVERT(DATETIME,@startDate, 102)
                 AND CONVERT(DATETIME, @endDate, 102)
Andriy M
  • 76,112
  • 17
  • 94
  • 154
Joshi
  • 51
  • 6
0

This works in SQL Query Analyzer:

SELECT CONVERT(DATETIME, '11-OCT-2011', 106)

I'm guessing your issue is how your adding your date to a SQL string. I'm guessing you have something like this in your ASP code...

strSQL = "SELECT * FROM tbl_Master " & _
         "WHERE DoDate BETWEEN CONVERT(DATETIME, '"&startDate&"', 106) AND CONVERT(DATETIME, '"&endDate&"', 106) "

If so, that should work, if it isn't please give us the strSQL text. Have you tried putting a spaces between your ampersands? '" & startDate & "'

Daryl
  • 18,592
  • 9
  • 78
  • 145
  • "SELECT * FROM DataReport WHERE location like '%" & slocation & "%' AND DATE BETWEEN #" & Format(datefrom,"mm/dd/yyyy") & "# and #" & Format(dateto,"mm/dd/yyyy") & "#;"...i try this code but mismatch format..zzz – GreenCat Nov 02 '11 at 08:00
  • What does that SQL string return? Can you do a message box with the text value so you can get the actual SQL query that you're sending to the database? – Daryl Nov 02 '11 at 12:49
  • can you reply give me your email sir so that i can send you my full code..thanks – GreenCat Nov 02 '11 at 13:11
  • If you want to share your code, use code paste or something like it to share. I may not be able to answer your question, or look through your code. http://codepaste.dev7studios.com/ – Daryl Nov 02 '11 at 13:54