473,324 Members | 2,541 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Dates

Hi,

I have made a search page on which users can search for documents. They can
search by documentnumber, customername,... and also by date. Now the problem
is that when a date is entered it doesn't show anything.

When I look at the SQL server Enterprise management and make a query there,
the following works:
select *
from TBL_Bestanden_Zoeken
where (datum='2005-12-31')

When I code it like that in my asp.net site, that when the field is not
empty, it searches for the documents with date='2005-12-31' then it doesn't
give any results.

What can I do?

Fré
May 4 '06 #1
14 1467
I've had problems with date format before.

With SQL Server it usualy expects MM/DD/YYYY by default (I think)

Try changing the date format, and see what happens.

May 4 '06 #2
Do you just send the date as a string to the database, or is it
converted to a DateTime at any stage?

Frederik Vanderhaeghe wrote:
Hi,

I have made a search page on which users can search for documents. They can
search by documentnumber, customername,... and also by date. Now the problem
is that when a date is entered it doesn't show anything.

When I look at the SQL server Enterprise management and make a query there,
the following works:
select *
from TBL_Bestanden_Zoeken
where (datum='2005-12-31')

When I code it like that in my asp.net site, that when the field is not
empty, it searches for the documents with date='2005-12-31' then it doesn't
give any results.

What can I do?

Fré

May 4 '06 #3
I actually use the calendar object of asp.net. So the select is:
select *
from TBL_Bestanden_Zoeken
where datum = '" & kalender.SelectedDate & "'

But when I search in the SQL Server itself, I can't do this:
select *
from TBL_Bestanden_Zoeken
where datum = '31/12/2005'

The result of kalender.SelectedDate is '31/12/2005'. But when I search for
datum = '2005/12/31' it works, but only on the SQL Server, not when I
hardcode it in ASP.Net.

It's a very weird thing

Fré

"Göran Andersson" <gu***@guffa.com> wrote in message
news:ud**************@TK2MSFTNGP02.phx.gbl...
Do you just send the date as a string to the database, or is it converted
to a DateTime at any stage?

Frederik Vanderhaeghe wrote:
Hi,

I have made a search page on which users can search for documents. They
can search by documentnumber, customername,... and also by date. Now the
problem is that when a date is entered it doesn't show anything.

When I look at the SQL server Enterprise management and make a query
there, the following works:
select *
from TBL_Bestanden_Zoeken
where (datum='2005-12-31')

When I code it like that in my asp.net site, that when the field is not
empty, it searches for the documents with date='2005-12-31' then it
doesn't give any results.

What can I do?

Fré

May 4 '06 #4
You are implicitly converting the date to a string, that means that it's
using the culture settings of the current thread. You should
specifically convert the date to a string, and specify how it should be
converted. You can use a CultureInfo object, a DateTimeFormat object or
a specific format string.

I suggest that you use the ISO 8601 date format. It's unambigous,
contrary to the dd/MM/yyyy and MM/dd/yyyy formats, that will depend on
the culture settings of the database server.

....
where datum = '" + kalender.SelectedDate.ToString("yyyy-MM-dd") + "'
....

Frederik Vanderhaeghe wrote:
I actually use the calendar object of asp.net. So the select is:
select *
from TBL_Bestanden_Zoeken
where datum = '" & kalender.SelectedDate & "'

But when I search in the SQL Server itself, I can't do this:
select *
from TBL_Bestanden_Zoeken
where datum = '31/12/2005'

The result of kalender.SelectedDate is '31/12/2005'. But when I search for
datum = '2005/12/31' it works, but only on the SQL Server, not when I
hardcode it in ASP.Net.

It's a very weird thing

Fré

"Göran Andersson" <gu***@guffa.com> wrote in message
news:ud**************@TK2MSFTNGP02.phx.gbl...
Do you just send the date as a string to the database, or is it converted
to a DateTime at any stage?

Frederik Vanderhaeghe wrote:
Hi,

I have made a search page on which users can search for documents. They
can search by documentnumber, customername,... and also by date. Now the
problem is that when a date is entered it doesn't show anything.

When I look at the SQL server Enterprise management and make a query
there, the following works:
select *
from TBL_Bestanden_Zoeken
where (datum='2005-12-31')

When I code it like that in my asp.net site, that when the field is not
empty, it searches for the documents with date='2005-12-31' then it
doesn't give any results.

What can I do?

Fré


May 4 '06 #5
But in my SQL Server the field is of the type 'Datetime'

Fré
"Göran Andersson" <gu***@guffa.com> wrote in message
news:e3**************@TK2MSFTNGP02.phx.gbl...
You are implicitly converting the date to a string, that means that it's
using the culture settings of the current thread. You should specifically
convert the date to a string, and specify how it should be converted. You
can use a CultureInfo object, a DateTimeFormat object or a specific format
string.

I suggest that you use the ISO 8601 date format. It's unambigous, contrary
to the dd/MM/yyyy and MM/dd/yyyy formats, that will depend on the culture
settings of the database server.

...
where datum = '" + kalender.SelectedDate.ToString("yyyy-MM-dd") + "'
...

Frederik Vanderhaeghe wrote:
I actually use the calendar object of asp.net. So the select is:
select *
from TBL_Bestanden_Zoeken
where datum = '" & kalender.SelectedDate & "'

But when I search in the SQL Server itself, I can't do this:
select *
from TBL_Bestanden_Zoeken
where datum = '31/12/2005'

The result of kalender.SelectedDate is '31/12/2005'. But when I search
for datum = '2005/12/31' it works, but only on the SQL Server, not when I
hardcode it in ASP.Net.

It's a very weird thing

Fré

"Göran Andersson" <gu***@guffa.com> wrote in message
news:ud**************@TK2MSFTNGP02.phx.gbl...
Do you just send the date as a string to the database, or is it
converted to a DateTime at any stage?

Frederik Vanderhaeghe wrote:
Hi,

I have made a search page on which users can search for documents. They
can search by documentnumber, customername,... and also by date. Now
the problem is that when a date is entered it doesn't show anything.

When I look at the SQL server Enterprise management and make a query
there, the following works:
select *
from TBL_Bestanden_Zoeken
where (datum='2005-12-31')

When I code it like that in my asp.net site, that when the field is not
empty, it searches for the documents with date='2005-12-31' then it
doesn't give any results.

What can I do?

Fré


May 4 '06 #6
That doesn't matter. The SQL query is a string, so the date in it is a
part of the string, not a separate DateTime value.

Frederik Vanderhaeghe wrote:
But in my SQL Server the field is of the type 'Datetime'

Fré
"Göran Andersson" <gu***@guffa.com> wrote in message
news:e3**************@TK2MSFTNGP02.phx.gbl...
You are implicitly converting the date to a string, that means that it's
using the culture settings of the current thread. You should specifically
convert the date to a string, and specify how it should be converted. You
can use a CultureInfo object, a DateTimeFormat object or a specific format
string.

I suggest that you use the ISO 8601 date format. It's unambigous, contrary
to the dd/MM/yyyy and MM/dd/yyyy formats, that will depend on the culture
settings of the database server.

...
where datum = '" + kalender.SelectedDate.ToString("yyyy-MM-dd") + "'
...

Frederik Vanderhaeghe wrote:
I actually use the calendar object of asp.net. So the select is:
select *
from TBL_Bestanden_Zoeken
where datum = '" & kalender.SelectedDate & "'

But when I search in the SQL Server itself, I can't do this:
select *
from TBL_Bestanden_Zoeken
where datum = '31/12/2005'

The result of kalender.SelectedDate is '31/12/2005'. But when I search
for datum = '2005/12/31' it works, but only on the SQL Server, not when I
hardcode it in ASP.Net.

It's a very weird thing

Fré

"Göran Andersson" <gu***@guffa.com> wrote in message
news:ud**************@TK2MSFTNGP02.phx.gbl...
Do you just send the date as a string to the database, or is it
converted to a DateTime at any stage?

Frederik Vanderhaeghe wrote:
> Hi,
>
> I have made a search page on which users can search for documents. They
> can search by documentnumber, customername,... and also by date. Now
> the problem is that when a date is entered it doesn't show anything.
>
> When I look at the SQL server Enterprise management and make a query
> there, the following works:
> select *
> from TBL_Bestanden_Zoeken
> where (datum='2005-12-31')
>
> When I code it like that in my asp.net site, that when the field is not
> empty, it searches for the documents with date='2005-12-31' then it
> doesn't give any results.
>
> What can I do?
>
> Fré

May 4 '06 #7
Result:
Server Error in '/ZoekSite' Application.
--------------------------------------------------------------------------------

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The conversion of a
char data type to a datetime data type resulted in an out-of-range datetime
value.

Source Error:

An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.]
System.Data.SqlClient.SqlDataReader.Read() +176
System.Data.Common.DbDataAdapter.FillLoadDataRow(S chemaMapping mapping)
+175
System.Data.Common.DbDataAdapter.FillFromReader(Ob ject data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue) +260
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable,
IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +129
System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
+36
ZoekSite.WebForm1.SQLUitvoeren(String orderby) in
D:\Inetpub\ASP\ZoekSite\Zoekpagina.aspx.vb:303
ZoekSite.WebForm1.btnzoeken_Click(Object sender, EventArgs e) in
D:\Inetpub\ASP\ZoekSite\Zoekpagina.aspx.vb:93
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292

"Göran Andersson" <gu***@guffa.com> wrote in message
news:O9**************@TK2MSFTNGP02.phx.gbl...
That doesn't matter. The SQL query is a string, so the date in it is a
part of the string, not a separate DateTime value.

Frederik Vanderhaeghe wrote:
But in my SQL Server the field is of the type 'Datetime'

Fré
"Göran Andersson" <gu***@guffa.com> wrote in message
news:e3**************@TK2MSFTNGP02.phx.gbl...
You are implicitly converting the date to a string, that means that it's
using the culture settings of the current thread. You should
specifically convert the date to a string, and specify how it should be
converted. You can use a CultureInfo object, a DateTimeFormat object or
a specific format string.

I suggest that you use the ISO 8601 date format. It's unambigous,
contrary to the dd/MM/yyyy and MM/dd/yyyy formats, that will depend on
the culture settings of the database server.

...
where datum = '" + kalender.SelectedDate.ToString("yyyy-MM-dd") + "'
...

Frederik Vanderhaeghe wrote:
I actually use the calendar object of asp.net. So the select is:
select *
from TBL_Bestanden_Zoeken
where datum = '" & kalender.SelectedDate & "'

But when I search in the SQL Server itself, I can't do this:
select *
from TBL_Bestanden_Zoeken
where datum = '31/12/2005'

The result of kalender.SelectedDate is '31/12/2005'. But when I search
for datum = '2005/12/31' it works, but only on the SQL Server, not when
I hardcode it in ASP.Net.

It's a very weird thing

Fré

"Göran Andersson" <gu***@guffa.com> wrote in message
news:ud**************@TK2MSFTNGP02.phx.gbl...
> Do you just send the date as a string to the database, or is it
> converted to a DateTime at any stage?
>
> Frederik Vanderhaeghe wrote:
>> Hi,
>>
>> I have made a search page on which users can search for documents.
>> They can search by documentnumber, customername,... and also by date.
>> Now the problem is that when a date is entered it doesn't show
>> anything.
>>
>> When I look at the SQL server Enterprise management and make a query
>> there, the following works:
>> select *
>> from TBL_Bestanden_Zoeken
>> where (datum='2005-12-31')
>>
>> When I code it like that in my asp.net site, that when the field is
>> not empty, it searches for the documents with date='2005-12-31' then
>> it doesn't give any results.
>>
>> What can I do?
>>
>> Fré

May 4 '06 #8
Odd. I have never ever had any problems with a date in ISO 8601 format.
Then again I live in Sweden, one of the few contries in the world to
actually follow the international standard for dates...

You should use a parameterized query in a command object. That way you
don't have to bother with the date format.

Frederik Vanderhaeghe wrote:
Result:
Server Error in '/ZoekSite' Application.
--------------------------------------------------------------------------------

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The conversion of a
char data type to a datetime data type resulted in an out-of-range datetime
value.

Source Error:

An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.]
System.Data.SqlClient.SqlDataReader.Read() +176
System.Data.Common.DbDataAdapter.FillLoadDataRow(S chemaMapping mapping)
+175
System.Data.Common.DbDataAdapter.FillFromReader(Ob ject data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue) +260
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable,
IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +129
System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
+36
ZoekSite.WebForm1.SQLUitvoeren(String orderby) in
D:\Inetpub\ASP\ZoekSite\Zoekpagina.aspx.vb:303
ZoekSite.WebForm1.btnzoeken_Click(Object sender, EventArgs e) in
D:\Inetpub\ASP\ZoekSite\Zoekpagina.aspx.vb:93
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292

"Göran Andersson" <gu***@guffa.com> wrote in message
news:O9**************@TK2MSFTNGP02.phx.gbl...
That doesn't matter. The SQL query is a string, so the date in it is a
part of the string, not a separate DateTime value.

Frederik Vanderhaeghe wrote:
But in my SQL Server the field is of the type 'Datetime'

Fré
"Göran Andersson" <gu***@guffa.com> wrote in message
news:e3**************@TK2MSFTNGP02.phx.gbl...
You are implicitly converting the date to a string, that means that it's
using the culture settings of the current thread. You should
specifically convert the date to a string, and specify how it should be
converted. You can use a CultureInfo object, a DateTimeFormat object or
a specific format string.

I suggest that you use the ISO 8601 date format. It's unambigous,
contrary to the dd/MM/yyyy and MM/dd/yyyy formats, that will depend on
the culture settings of the database server.

...
where datum = '" + kalender.SelectedDate.ToString("yyyy-MM-dd") + "'
...

Frederik Vanderhaeghe wrote:
> I actually use the calendar object of asp.net. So the select is:
> select *
> from TBL_Bestanden_Zoeken
> where datum = '" & kalender.SelectedDate & "'
>
> But when I search in the SQL Server itself, I can't do this:
> select *
> from TBL_Bestanden_Zoeken
> where datum = '31/12/2005'
>
> The result of kalender.SelectedDate is '31/12/2005'. But when I search
> for datum = '2005/12/31' it works, but only on the SQL Server, not when
> I hardcode it in ASP.Net.
>
> It's a very weird thing
>
> Fré
>
> "Göran Andersson" <gu***@guffa.com> wrote in message
> news:ud**************@TK2MSFTNGP02.phx.gbl...
>> Do you just send the date as a string to the database, or is it
>> converted to a DateTime at any stage?
>>
>> Frederik Vanderhaeghe wrote:
>>> Hi,
>>>
>>> I have made a search page on which users can search for documents.
>>> They can search by documentnumber, customername,... and also by date.
>>> Now the problem is that when a date is entered it doesn't show
>>> anything.
>>>
>>> When I look at the SQL server Enterprise management and make a query
>>> there, the following works:
>>> select *
>>> from TBL_Bestanden_Zoeken
>>> where (datum='2005-12-31')
>>>
>>> When I code it like that in my asp.net site, that when the field is
>>> not empty, it searches for the documents with date='2005-12-31' then
>>> it doesn't give any results.
>>>
>>> What can I do?
>>>
>>> Fré


May 4 '06 #9
And how do I do that??

Fré

"Göran Andersson" <gu***@guffa.com> wrote in message
news:uC**************@TK2MSFTNGP03.phx.gbl...
Odd. I have never ever had any problems with a date in ISO 8601 format.
Then again I live in Sweden, one of the few contries in the world to
actually follow the international standard for dates...

You should use a parameterized query in a command object. That way you
don't have to bother with the date format.

Frederik Vanderhaeghe wrote:
Result:
Server Error in '/ZoekSite' Application.
--------------------------------------------------------------------------------

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The conversion of
a char data type to a datetime data type resulted in an out-of-range
datetime value.

Source Error:

An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.]
System.Data.SqlClient.SqlDataReader.Read() +176
System.Data.Common.DbDataAdapter.FillLoadDataRow(S chemaMapping
mapping) +175
System.Data.Common.DbDataAdapter.FillFromReader(Ob ject data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue) +260
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
+129
System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable) +36
ZoekSite.WebForm1.SQLUitvoeren(String orderby) in
D:\Inetpub\ASP\ZoekSite\Zoekpagina.aspx.vb:303
ZoekSite.WebForm1.btnzoeken_Click(Object sender, EventArgs e) in
D:\Inetpub\ASP\ZoekSite\Zoekpagina.aspx.vb:93
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108

System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData)
+33
System.Web.UI.Page.ProcessRequestMain() +1292

"Göran Andersson" <gu***@guffa.com> wrote in message
news:O9**************@TK2MSFTNGP02.phx.gbl...
That doesn't matter. The SQL query is a string, so the date in it is a
part of the string, not a separate DateTime value.

Frederik Vanderhaeghe wrote:
But in my SQL Server the field is of the type 'Datetime'

Fré
"Göran Andersson" <gu***@guffa.com> wrote in message
news:e3**************@TK2MSFTNGP02.phx.gbl...
> You are implicitly converting the date to a string, that means that
> it's using the culture settings of the current thread. You should
> specifically convert the date to a string, and specify how it should
> be converted. You can use a CultureInfo object, a DateTimeFormat
> object or a specific format string.
>
> I suggest that you use the ISO 8601 date format. It's unambigous,
> contrary to the dd/MM/yyyy and MM/dd/yyyy formats, that will depend on
> the culture settings of the database server.
>
> ...
> where datum = '" + kalender.SelectedDate.ToString("yyyy-MM-dd") + "'
> ...
>
> Frederik Vanderhaeghe wrote:
>> I actually use the calendar object of asp.net. So the select is:
>> select *
>> from TBL_Bestanden_Zoeken
>> where datum = '" & kalender.SelectedDate & "'
>>
>> But when I search in the SQL Server itself, I can't do this:
>> select *
>> from TBL_Bestanden_Zoeken
>> where datum = '31/12/2005'
>>
>> The result of kalender.SelectedDate is '31/12/2005'. But when I
>> search for datum = '2005/12/31' it works, but only on the SQL Server,
>> not when I hardcode it in ASP.Net.
>>
>> It's a very weird thing
>>
>> Fré
>>
>> "Göran Andersson" <gu***@guffa.com> wrote in message
>> news:ud**************@TK2MSFTNGP02.phx.gbl...
>>> Do you just send the date as a string to the database, or is it
>>> converted to a DateTime at any stage?
>>>
>>> Frederik Vanderhaeghe wrote:
>>>> Hi,
>>>>
>>>> I have made a search page on which users can search for documents.
>>>> They can search by documentnumber, customername,... and also by
>>>> date. Now the problem is that when a date is entered it doesn't
>>>> show anything.
>>>>
>>>> When I look at the SQL server Enterprise management and make a
>>>> query there, the following works:
>>>> select *
>>>> from TBL_Bestanden_Zoeken
>>>> where (datum='2005-12-31')
>>>>
>>>> When I code it like that in my asp.net site, that when the field is
>>>> not empty, it searches for the documents with date='2005-12-31'
>>>> then it doesn't give any results.
>>>>
>>>> What can I do?
>>>>
>>>> Fré


May 4 '06 #10
"Frederik Vanderhaeghe" <fr******************@gmail.com> wrote in message
news:O0**************@TK2MSFTNGP02.phx.gbl...
What can I do?


1) Use parameterised queries.

2) If you are actually building up the SQL dynamically, make sure you pass
your date in a TOTALLY UNAMBIGUOUS format. I always use dd MMM yyyy, which
is guaranteed to be evaluated as the same date irrespective of the locale,
regional settings, SQL Server installation options etc.

3) Be aware of the time portion of datetime and smalldatetime fields.

E.g. if you have a table where datetime fields are populated automatically
through the getdate() function, the values that get written to the database
will include the time portion too.

The clause "where (datum='2005-12-31')" is interpreted by SQL Server as
"show me all the records where the datum field contains 31 Dec 2005
00:00:00" - any records, say, where the datum field contains 31 Dec 2005
10:00:00 will not be returned.
May 4 '06 #11
Hi Mark

How can I use parameterised queries??

Fré
"Mark Rae" <ma**@markN-O-S-P-A-M.co.uk> wrote in message
news:eR*************@TK2MSFTNGP02.phx.gbl...
"Frederik Vanderhaeghe" <fr******************@gmail.com> wrote in message
news:O0**************@TK2MSFTNGP02.phx.gbl...
What can I do?


1) Use parameterised queries.

2) If you are actually building up the SQL dynamically, make sure you pass
your date in a TOTALLY UNAMBIGUOUS format. I always use dd MMM yyyy, which
is guaranteed to be evaluated as the same date irrespective of the locale,
regional settings, SQL Server installation options etc.

3) Be aware of the time portion of datetime and smalldatetime fields.

E.g. if you have a table where datetime fields are populated automatically
through the getdate() function, the values that get written to the
database will include the time portion too.

The clause "where (datum='2005-12-31')" is interpreted by SQL Server as
"show me all the records where the datum field contains 31 Dec 2005
00:00:00" - any records, say, where the datum field contains 31 Dec 2005
10:00:00 will not be returned.

May 4 '06 #12
Create an SqlCommand object and put the query in it. Use parameter names
like @Date in the query instead of the values (including the surrounding
apostrophes - the command will add them). Create SqlParameter objects
for the parameters and add them to the Parameters collecion of the command.

Frederik Vanderhaeghe wrote:
And how do I do that??

Fré

"Göran Andersson" <gu***@guffa.com> wrote in message
news:uC**************@TK2MSFTNGP03.phx.gbl...
Odd. I have never ever had any problems with a date in ISO 8601 format.
Then again I live in Sweden, one of the few contries in the world to
actually follow the international standard for dates...

You should use a parameterized query in a command object. That way you
don't have to bother with the date format.

Frederik Vanderhaeghe wrote:
Result:
Server Error in '/ZoekSite' Application.
--------------------------------------------------------------------------------

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The conversion of
a char data type to a datetime data type resulted in an out-of-range
datetime value.

Source Error:

An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.]
System.Data.SqlClient.SqlDataReader.Read() +176
System.Data.Common.DbDataAdapter.FillLoadDataRow(S chemaMapping
mapping) +175
System.Data.Common.DbDataAdapter.FillFromReader(Ob ject data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue) +260
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
+129
System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable) +36
ZoekSite.WebForm1.SQLUitvoeren(String orderby) in
D:\Inetpub\ASP\ZoekSite\Zoekpagina.aspx.vb:303
ZoekSite.WebForm1.btnzoeken_Click(Object sender, EventArgs e) in
D:\Inetpub\ASP\ZoekSite\Zoekpagina.aspx.vb:93
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108

System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData)
+33
System.Web.UI.Page.ProcessRequestMain() +1292

"Göran Andersson" <gu***@guffa.com> wrote in message
news:O9**************@TK2MSFTNGP02.phx.gbl...
That doesn't matter. The SQL query is a string, so the date in it is a
part of the string, not a separate DateTime value.

Frederik Vanderhaeghe wrote:
> But in my SQL Server the field is of the type 'Datetime'
>
> Fré
> "Göran Andersson" <gu***@guffa.com> wrote in message
> news:e3**************@TK2MSFTNGP02.phx.gbl...
>> You are implicitly converting the date to a string, that means that
>> it's using the culture settings of the current thread. You should
>> specifically convert the date to a string, and specify how it should
>> be converted. You can use a CultureInfo object, a DateTimeFormat
>> object or a specific format string.
>>
>> I suggest that you use the ISO 8601 date format. It's unambigous,
>> contrary to the dd/MM/yyyy and MM/dd/yyyy formats, that will depend on
>> the culture settings of the database server.
>>
>> ...
>> where datum = '" + kalender.SelectedDate.ToString("yyyy-MM-dd") + "'
>> ...
>>
>> Frederik Vanderhaeghe wrote:
>>> I actually use the calendar object of asp.net. So the select is:
>>> select *
>>> from TBL_Bestanden_Zoeken
>>> where datum = '" & kalender.SelectedDate & "'
>>>
>>> But when I search in the SQL Server itself, I can't do this:
>>> select *
>>> from TBL_Bestanden_Zoeken
>>> where datum = '31/12/2005'
>>>
>>> The result of kalender.SelectedDate is '31/12/2005'. But when I
>>> search for datum = '2005/12/31' it works, but only on the SQL Server,
>>> not when I hardcode it in ASP.Net.
>>>
>>> It's a very weird thing
>>>
>>> Fré
>>>
>>> "Göran Andersson" <gu***@guffa.com> wrote in message
>>> news:ud**************@TK2MSFTNGP02.phx.gbl...
>>>> Do you just send the date as a string to the database, or is it
>>>> converted to a DateTime at any stage?
>>>>
>>>> Frederik Vanderhaeghe wrote:
>>>>> Hi,
>>>>>
>>>>> I have made a search page on which users can search for documents.
>>>>> They can search by documentnumber, customername,... and also by
>>>>> date. Now the problem is that when a date is entered it doesn't
>>>>> show anything.
>>>>>
>>>>> When I look at the SQL server Enterprise management and make a
>>>>> query there, the following works:
>>>>> select *
>>>>> from TBL_Bestanden_Zoeken
>>>>> where (datum='2005-12-31')
>>>>>
>>>>> When I code it like that in my asp.net site, that when the field is
>>>>> not empty, it searches for the documents with date='2005-12-31'
>>>>> then it doesn't give any results.
>>>>>
>>>>> What can I do?
>>>>>
>>>>> Fré

May 4 '06 #13
"Frederik Vanderhaeghe" <fr******************@gmail.com> wrote in message
news:uU**************@TK2MSFTNGP05.phx.gbl...
Hi Mark

How can I use parameterised queries??


1) Launch your Internet browser (IE, FireFox, Netscape etc)

2) Go to http://www.google.com

3) Enter the text below in the box:

"SQL Server" parameterized query

4) Hit the button
May 4 '06 #14
I tried the following, in the Enterprise Manager I executed the query with
different date formats
Select *
From TBL_Bestanden_Zoeken
Where datum = ' '

It worked (gave results) with '12/31/2005' , '12-31-2005' , '12.31.2005' ,
'2005/12/31' , '2005-12-31' , '2005.12.31'
It didn't work with: '31/12/2005' , '31-12-2005' , '12.31-2005'

In my ASP.Net code I typed the following:
select * from TBL_Bestanden_Zoeken where datum = '12/31/2005' and tried it
with all the ones that worked in the Enterprise Manager
Not a single date format gave any results.

????

Fré

"Frederik Vanderhaeghe" <fr******************@gmail.com> wrote in message
news:O0**************@TK2MSFTNGP02.phx.gbl...
Hi,

I have made a search page on which users can search for documents. They
can search by documentnumber, customername,... and also by date. Now the
problem is that when a date is entered it doesn't show anything.

When I look at the SQL server Enterprise management and make a query
there, the following works:
select *
from TBL_Bestanden_Zoeken
where (datum='2005-12-31')

When I code it like that in my asp.net site, that when the field is not
empty, it searches for the documents with date='2005-12-31' then it
doesn't give any results.

What can I do?

Fré

May 4 '06 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Riley | last post by:
The date fields being saved by a VB program were being saved as #2003-11-22#. For reasons unknown to me these dates began to be saved as "11/22/2003" All of these dates were made dates with the...
7
by: Alistair | last post by:
diary_date = request.form("diary_date") - (from a populated drop down list) strSQL = "SELECT saz_title, saz_text from saz_details where saz_date =#" & diary_date & "#" a response.write...
5
by: PW | last post by:
<rant> Sorry guys, but I just have to whinge. Dates in ASP are a total pain in the butt! I seem to get caught out so many times. I realise its my own fault, but going from the posts in this...
10
by: Colin Steadman | last post by:
I'm a stupid ASP programmer and I dont do Javascript (except for very simple tasks anyway), and I'm in a bit of a predicament. I've used a javascript table sorting script from here: ...
1
by: Don Sealer | last post by:
I have a report that includes 5 different subreports. I'd like to be able to open this report using a date function (Start Date and End Date). I'd like all five subreports to show the data from...
2
by: Rachel Suddeth | last post by:
Is there a way to have the non-selectable dates (those before MinDate and after MaxDate) draw differently so my users can see right away what dates aren't allowed? I'm not seeing it... ...
12
by: Dixie | last post by:
I am trying to calculate the number of workdays between two dates with regards to holidays as well. I have used Arvin Meyer's code on the Access Web, but as I am in Australia and my date format is...
1
by: pitfour.ferguson | last post by:
My dbase has the start date and end date of each visit. How can I ask Access to list the day of the week of the start (easy), end (easy) and, more importantly, the dates of the visit itself - ie...
7
by: evilcowstare via AccessMonster.com | last post by:
Hi, I have searched the forum for answers on this and to be honest as a novice I find it a bit confusing so apologies if it is simple. There are some searches that I want to apply to my database....
2
by: Jim Carlock | last post by:
(1) Does PHP provide any way to handle dates prior to 1980? I know there's problems with Microsoft Windows NT and all Windows NT operating systems will allow a date prior to 1980 to be placed...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shćllîpôpď 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.