CODEWITHSUNDEEP
sqlsql-serverasp-classic
GreenCat
GreenCat

Reputation: 167

SQL Server Datetime convert from string

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-

Upvotes: 2

Views: 1514

Answers (4)

Joshi
Joshi

Reputation: 51

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)

Upvotes: 0

Daryl
Daryl

Reputation: 18895

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 & "'

Upvotes: 0

Daryl
Daryl

Reputation: 18895

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)

Upvotes: 0

paparush
paparush

Reputation: 1348

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)

Upvotes: 2

Related Questions