Dates with SQL Server

[elibb]

I'm trying to get an interval of dates from a visual basic aplication, from a database in sql server 7.0, I've been trying a lot of things on my Select but i get that
"the convertion of a char data type to a datetyme data type resulted in an out-of-range datetime value"

the code im using is this: (well one of the codes, ive tryed like 10 different ways)

in Visual Basic 6.0

datebegin=#01/09/2001# ** example
dateend= #20/09/2001#

adodc1.Recordsource= &quot;Select convert(smallDateTime, Date) from Table where convert(DateTime, Date) > '&quot; & Datebegin & &quot;' and convert(DateTime, Date) < &quot; & Dateend & &quot;'&quot;

i should get all the registers of sepember, but its not working

i hope someone can help me, its driving me crazy!!!

thank You

Eli

 

[tlbroadbent]

There are several ways to overcome the date &quot;problems&quot; one encounters in SQL Server. First thing is to make sure you understand the data types in use. Is the Date column defined as a character data type? If so, that can cause problems. People don't understand how SQL Server stores dates so they often store them as character data types rather than datetime. If possible, make sure dates are stored in datetime columns.

NOTE: If the column, Date, is already defined as Datetime or SmallDatetime, you don't need to convert it in the where clause.

1) Use the &quot;MMM DD YYYY&quot; format when querying SQL Server.

' Use format(datebegin, &quot;mmm dd yyyy&quot
datebegin = &quot;Sep 1 2001&quot;
dateend = &quot;Sep 20 2001&quot;

2) Convert all dates to the same data type in SQL Server.

datebegin=#01/09/2001#
dateend= #20/09/2001#

' This assumes the dates are in British format
adodc1.Recordsource = &quot;Select convert(smallDateTime,Date,103) &quot; & _
&quot;From Table Where Convert(DateTime,Date,103) Between &quot; & _
&quot;Convert(datetime,'&quot; & datebegin & &quot;',103) And &quot; & _
&quot;Convert(datetime,'&quot; & dateend & &quot;',103)&quot;

3) Use ODBC date formats.

datebegin = &quot;2001-09-01&quot;
dateend = &quot;2001-09-20&quot;

adodc1.Recordsource = &quot;Select Date &quot; & _
&quot;From Table Where Date Between &quot; & _
&quot;{d '&quot; & datebegin & &quot;'} And {d '&quot; & dateend & &quot;'}&quot;

4) Use U.S. date formats. SQL Sever usually understands these formats.

datebegin = &quot;9/1/2001&quot;
dateend = &quot;9/20/2001&quot; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[elibb]

thank you very very very much
its working now.