Error trying to execute sql statement between two dates

Question

The SQL:

dim sql
sSql = "SELECT * FROM [dbo].[table] WHERE [sent] = 1 and datesent between '" & dStartDate & "' and '"  & dFinishDate & "'"
response.write(sSql)
set oRs = oConn.execute(sSql)

When i execute this sql in sql server 2008 it works fine. However, when i execute it within my application i get error:

Microsoft OLE DB Provider for SQL Server error '80040e07'

The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value.

Is there something different i have to do in the application? thanks

Use query parameters to avoid type errors and SQL injection... — Marco
– Marco, Commented Jan 23, 2012 at 13:00
Have you tried using yyyyMMdd dates? This usually avoids any locale issues. Your query then becomes ... between '20120101' and '20120127'. You are probably getting an error because it's assuming dd/MM/yyyy and month 27 is undefined! — dash
– dash, Commented Jan 23, 2012 at 13:04

Mitch Wheat · Accepted Answer · 2012-01-23 13:16:45Z

5

Have you tried using ISO format dates?

SELECT * FROM [dbo].[Table]  
WHERE [sent] = 1 and datesent between '20120101' and '20120127'

(Strictly speaking an ISO 8601 date for sql server is yyyy-mm-ddThh:mm:ss[.mmm])

Side Note: always use ISO format dates when you output to text (or have literals); that way they can be read unambiguously on all systems).

SQL Server ISO 8601 Format Dates

http://msdn.microsoft.com/en-us/library/ms190977(v=sql.90).aspx

edited Jan 23, 2012 at 13:16

answered Jan 23, 2012 at 13:00

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

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

1 Comment

Damien_The_Unbeliever Over a year ago

Slashed format (and dashed) doesn't work if you're targetting a datetime. This produces an error:set language british;select CONVERT(datetime,'2012/01/27'). If only specifying a date, omit separators completely. All works without issue against a datetime2.

Oleg Dok · Accepted Answer · 2012-01-23 12:59:09Z

1

SELECT * 
FROM [dbo].[Table] 
WHERE 
    [sent] = 1 
and datesent between CONVERT(datetime, '01/01/2012', 101) and CONVERT(datetime, '01/27/2012', 101)

OR

Your column datesent is not DATETIME datatype

answered Jan 23, 2012 at 12:59

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Comments

Andomar · Accepted Answer · 2012-01-23 12:59:16Z

0

Check your datetime setting.

set dateformat mdy
select CAST('01/27/2012' as datetime)
set dateformat dmy
select CAST('01/27/2012' as datetime) --> Exception

You can display the current setting with:

DBCC USEROPTIONS

answered Jan 23, 2012 at 12:59

Andomar

239k55 gold badges387 silver badges412 bronze badges

Comments

score 0 · Accepted Answer · 2012-01-23 13:43:43Z

0

You should always use the ISO-formats 'YYYYMMDD' for your dates, as this will always be parsed correctly by SQL Server, regardless of localization setting.

edited Jan 23, 2012 at 13:43

answered Jan 23, 2012 at 13:01

user806549

3 Comments

Damien_The_Unbeliever Over a year ago

Ahem. set language british;select CONVERT(datetime,'2012-01-27') produces an error in SQL Server.

user806549 Over a year ago

Interesting, thanks. I have for a long time only accepted without the dashes, but recently started working at a new place where they insisted that WITH dashes was correct. Until now, I've been unable to come up with a counter-example, so I'd modified my stance to accept both variants.

dash Over a year ago

set language british;select CONVERT(datetime,'20120127') works just fine locally - it's those pesky dash[es] ;-)

Collectives™ on Stack Overflow

Error trying to execute sql statement between two dates

4 Answers 4

1 Comment

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related