WHAT'S WRONG???

[NiteCrawlr]

Could somebody please tell me what is wrong with this Select???

Select * from ngrm10 where pcc_grm = '&pcc&' AND dte_grm >= #&quot; & data_ini & &quot;# AND dte_grm <= #&quot; & data_fim & &quot;#

I'm using it in an ASP page, it is returning the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '#'.

/rayan/resultado.asp, line 69


I don't know what is wrong.

Thanks a lot

Frederico

 

[tlbroadbent]

Don't use # to delimit dates. Use ' (single quote) instead. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[NiteCrawlr]

but if I try with single quotes it returns the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

/rayan/resultado.asp, line 69

here is the select line:

Select * from ngrm10 where pcc_grm = '&pcc&' AND (dte_grm >= ' & data_ini & ' AND dte_grm <= ' & data_fim & ')

 

[NiteCrawlr]

Ohh... DTE_GRM is set as a DATETIME in SQL SERVER

 

[tlbroadbent]

Select * from ngrm10 where pcc_grm = '&pcc&' AND (dte_grm >= ' & data_ini & ' AND dte_grm <= ' & data_fim & ')

Does your ASP code substitute values for data_ini and dte_fim? If the application is sending the SQL Select statement to SQL Server exactly as you typed it, it will error because to SQL Server data_ini and dte_fim are just character strings and they don't convert to dates. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[NiteCrawlr]

This is what how I am putting values:

dia_ini = Request.Form(&quot;dia_ini&quot
mes_ini = Request.Form(&quot;mes_ini&quot
ano_ini = Request.Form(&quot;ano_ini&quot

data_ini = ano_ini&&quot;/&quot;&mes_ini&&quot;/&quot;&dia_ini

data_ini = formatdatetime(data_ini,vbshortdate)

dia_fim = Request.Form(&quot;dia_fim&quot
mes_fim = Request.Form(&quot;mes_fim&quot
ano_fim = Request.Form(&quot;ano_fim&quot

data_fim = ano_fim&&quot;/&quot;&mes_fim&&quot;/&quot;&dia_fim

data_fim = formatdatetime(data_fim,vbshortdate)

 

[tlbroadbent]

The question you didn't answer is how you create the SQL select statement. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[NiteCrawlr]

I found the problem, but I don't know how to solve it.

My Regional Settings is set to show date (YYYY-MM-DD) when I put a date in a SQL SERVER table, I have to use the YYYY-MM-DD format, but when I try to do a Select comand, I can't use that format, it forces me to use YYYY-DD-MM.

How can I solve this problem??

 

[NiteCrawlr]

This is how the date look like in the table:
2001-12-13

When I use the select command I must do it like:
Select * from ngrm10 where DTE_GRM >='2001-13-12'

but if I do it like:
Select * from ngrm10 where DTE_GRM >='2001-12-13'

this error is shown:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Thank you,

Frederico