Dates

[Frederik Vanderhaeghe]

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_Z oeken
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é

[Richard Brown]

Re: Dates

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.

[Göran Andersson]

Re: Dates

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:[color=blue]
> 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_Z oeken
> 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é
>
>[/color]

[Frederik Vanderhaeghe]

Re: Dates

I actually use the calendar object of asp.net. So the select is:
select *
from TBL_Bestanden_Z oeken
where datum = '" & kalender.Select edDate & "'

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

The result of kalender.Select edDate 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" <guffa@guffa.co m> wrote in message
news:udW9Zx1bGH A.3908@TK2MSFTN GP02.phx.gbl...[color=blue]
> 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:[color=green]
>> 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_Z oeken
>> 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é[/color][/color]

[Göran Andersson]

Re: Dates

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.Select edDate.ToString ("yyyy-MM-dd") + "'
....

Frederik Vanderhaeghe wrote:[color=blue]
> I actually use the calendar object of asp.net. So the select is:
> select *
> from TBL_Bestanden_Z oeken
> where datum = '" & kalender.Select edDate & "'
>
> But when I search in the SQL Server itself, I can't do this:
> select *
> from TBL_Bestanden_Z oeken
> where datum = '31/12/2005'
>
> The result of kalender.Select edDate 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" <guffa@guffa.co m> wrote in message
> news:udW9Zx1bGH A.3908@TK2MSFTN GP02.phx.gbl...[color=green]
>> 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:[color=darkred]
>>> 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_Z oeken
>>> 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é[/color][/color]
>
>[/color]

[Frederik Vanderhaeghe]

Re: Dates

But in my SQL Server the field is of the type 'Datetime'

Fré
"Göran Andersson" <guffa@guffa.co m> wrote in message
news:e3xQBL2bGH A.4900@TK2MSFTN GP02.phx.gbl...[color=blue]
> 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.Select edDate.ToString ("yyyy-MM-dd") + "'
> ...
>
> Frederik Vanderhaeghe wrote:[color=green]
>> I actually use the calendar object of asp.net. So the select is:
>> select *
>> from TBL_Bestanden_Z oeken
>> where datum = '" & kalender.Select edDate & "'
>>
>> But when I search in the SQL Server itself, I can't do this:
>> select *
>> from TBL_Bestanden_Z oeken
>> where datum = '31/12/2005'
>>
>> The result of kalender.Select edDate 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" <guffa@guffa.co m> wrote in message
>> news:udW9Zx1bGH A.3908@TK2MSFTN GP02.phx.gbl...[color=darkred]
>>> 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_Z oeken
>>>> 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é[/color]
>>[/color][/color]

[Göran Andersson]

Re: Dates

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:[color=blue]
> But in my SQL Server the field is of the type 'Datetime'
>
> Fré
> "Göran Andersson" <guffa@guffa.co m> wrote in message
> news:e3xQBL2bGH A.4900@TK2MSFTN GP02.phx.gbl...[color=green]
>> 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.Select edDate.ToString ("yyyy-MM-dd") + "'
>> ...
>>
>> Frederik Vanderhaeghe wrote:[color=darkred]
>>> I actually use the calendar object of asp.net. So the select is:
>>> select *
>>> from TBL_Bestanden_Z oeken
>>> where datum = '" & kalender.Select edDate & "'
>>>
>>> But when I search in the SQL Server itself, I can't do this:
>>> select *
>>> from TBL_Bestanden_Z oeken
>>> where datum = '31/12/2005'
>>>
>>> The result of kalender.Select edDate 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" <guffa@guffa.co m> wrote in message
>>> news:udW9Zx1bGH A.3908@TK2MSFTN GP02.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_Z oeken
>>>>> 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é[/color][/color]
>[/color]

[Frederik Vanderhaeghe]

Re: Dates

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.Sql Client.SqlExcep tion: 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.Sql Client.SqlDataR eader.Read() +176
System.Data.Com mon.DbDataAdapt er.FillLoadData Row(SchemaMappi ng mapping)
+175
System.Data.Com mon.DbDataAdapt er.FillFromRead er(Object data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterCo lumn, Object parentChapterVa lue) +260
System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, String srcTable,
IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +129
System.Data.Com mon.DbDataAdapt er.FillFromComm and(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, String srcTable)
+36
ZoekSite.WebFor m1.SQLUitvoeren (String orderby) in
D:\Inetpub\ASP\ ZoekSite\Zoekpa gina.aspx.vb:30 3
ZoekSite.WebFor m1.btnzoeken_Cl ick(Object sender, EventArgs e) in
D:\Inetpub\ASP\ ZoekSite\Zoekpa gina.aspx.vb:93
System.Web.UI.W ebControls.Butt on.OnClick(Even tArgs e) +108
System.Web.UI.W ebControls.Butt on.System.Web.U I.IPostBackEven tHandler.RaiseP ostBackEvent(St ring
eventArgument) +57
System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData) +33
System.Web.UI.P age.ProcessRequ estMain() +1292



"Göran Andersson" <guffa@guffa.co m> wrote in message
news:O98acg2bGH A.4032@TK2MSFTN GP02.phx.gbl...[color=blue]
> 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:[color=green]
>> But in my SQL Server the field is of the type 'Datetime'
>>
>> Fré
>> "Göran Andersson" <guffa@guffa.co m> wrote in message
>> news:e3xQBL2bGH A.4900@TK2MSFTN GP02.phx.gbl...[color=darkred]
>>> 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.Select edDate.ToString ("yyyy-MM-dd") + "'
>>> ...
>>>
>>> Frederik Vanderhaeghe wrote:
>>>> I actually use the calendar object of asp.net. So the select is:
>>>> select *
>>>> from TBL_Bestanden_Z oeken
>>>> where datum = '" & kalender.Select edDate & "'
>>>>
>>>> But when I search in the SQL Server itself, I can't do this:
>>>> select *
>>>> from TBL_Bestanden_Z oeken
>>>> where datum = '31/12/2005'
>>>>
>>>> The result of kalender.Select edDate 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" <guffa@guffa.co m> wrote in message
>>>> news:udW9Zx1bGH A.3908@TK2MSFTN GP02.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_Z oeken
>>>>>> 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é[/color]
>>[/color][/color]

[Göran Andersson]

Re: Dates

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:[color=blue]
> 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.Sql Client.SqlExcep tion: 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.Sql Client.SqlDataR eader.Read() +176
> System.Data.Com mon.DbDataAdapt er.FillLoadData Row(SchemaMappi ng mapping)
> +175
> System.Data.Com mon.DbDataAdapt er.FillFromRead er(Object data, String
> srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
> DataColumn parentChapterCo lumn, Object parentChapterVa lue) +260
> System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, String srcTable,
> IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +129
> System.Data.Com mon.DbDataAdapt er.FillFromComm and(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior) +304
> System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, Int32 startRecord,
> Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
> behavior) +77
> System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, String srcTable)
> +36
> ZoekSite.WebFor m1.SQLUitvoeren (String orderby) in
> D:\Inetpub\ASP\ ZoekSite\Zoekpa gina.aspx.vb:30 3
> ZoekSite.WebFor m1.btnzoeken_Cl ick(Object sender, EventArgs e) in
> D:\Inetpub\ASP\ ZoekSite\Zoekpa gina.aspx.vb:93
> System.Web.UI.W ebControls.Butt on.OnClick(Even tArgs e) +108
> System.Web.UI.W ebControls.Butt on.System.Web.U I.IPostBackEven tHandler.RaiseP ostBackEvent(St ring
> eventArgument) +57
> System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
> sourceControl, String eventArgument) +18
> System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData) +33
> System.Web.UI.P age.ProcessRequ estMain() +1292
>
>
>
> "Göran Andersson" <guffa@guffa.co m> wrote in message
> news:O98acg2bGH A.4032@TK2MSFTN GP02.phx.gbl...[color=green]
>> 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:[color=darkred]
>>> But in my SQL Server the field is of the type 'Datetime'
>>>
>>> Fré
>>> "Göran Andersson" <guffa@guffa.co m> wrote in message
>>> news:e3xQBL2bGH A.4900@TK2MSFTN GP02.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.Select edDate.ToString ("yyyy-MM-dd") + "'
>>>> ...
>>>>
>>>> Frederik Vanderhaeghe wrote:
>>>>> I actually use the calendar object of asp.net. So the select is:
>>>>> select *
>>>>> from TBL_Bestanden_Z oeken
>>>>> where datum = '" & kalender.Select edDate & "'
>>>>>
>>>>> But when I search in the SQL Server itself, I can't do this:
>>>>> select *
>>>>> from TBL_Bestanden_Z oeken
>>>>> where datum = '31/12/2005'
>>>>>
>>>>> The result of kalender.Select edDate 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" <guffa@guffa.co m> wrote in message
>>>>> news:udW9Zx1bGH A.3908@TK2MSFTN GP02.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_Z oeken
>>>>>>> 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é[/color][/color]
>
>[/color]

[Frederik Vanderhaeghe]

Re: Dates

And how do I do that??

Fré

"Göran Andersson" <guffa@guffa.co m> wrote in message
news:uCXb2u2bGH A.3484@TK2MSFTN GP03.phx.gbl...[color=blue]
> 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:[color=green]
>> 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.Sql Client.SqlExcep tion: 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.Sql Client.SqlDataR eader.Read() +176
>> System.Data.Com mon.DbDataAdapt er.FillLoadData Row(SchemaMappi ng
>> mapping) +175
>> System.Data.Com mon.DbDataAdapt er.FillFromRead er(Object data, String
>> srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
>> DataColumn parentChapterCo lumn, Object parentChapterVa lue) +260
>> System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, String
>> srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
>> +129
>> System.Data.Com mon.DbDataAdapt er.FillFromComm and(Object data, Int32
>> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
>> CommandBehavior behavior) +304
>> System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, Int32
>> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
>> CommandBehavior behavior) +77
>> System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, String
>> srcTable) +36
>> ZoekSite.WebFor m1.SQLUitvoeren (String orderby) in
>> D:\Inetpub\ASP\ ZoekSite\Zoekpa gina.aspx.vb:30 3
>> ZoekSite.WebFor m1.btnzoeken_Cl ick(Object sender, EventArgs e) in
>> D:\Inetpub\ASP\ ZoekSite\Zoekpa gina.aspx.vb:93
>> System.Web.UI.W ebControls.Butt on.OnClick(Even tArgs e) +108
>>
>> System.Web.UI.W ebControls.Butt on.System.Web.U I.IPostBackEven tHandler.RaiseP ostBackEvent(St ring
>> eventArgument) +57
>> System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
>> sourceControl, String eventArgument) +18
>> System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData)
>> +33
>> System.Web.UI.P age.ProcessRequ estMain() +1292
>>
>>
>>
>> "Göran Andersson" <guffa@guffa.co m> wrote in message
>> news:O98acg2bGH A.4032@TK2MSFTN GP02.phx.gbl...[color=darkred]
>>> 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" <guffa@guffa.co m> wrote in message
>>>> news:e3xQBL2bGH A.4900@TK2MSFTN GP02.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.Select edDate.ToString ("yyyy-MM-dd") + "'
>>>>> ...
>>>>>
>>>>> Frederik Vanderhaeghe wrote:
>>>>>> I actually use the calendar object of asp.net. So the select is:
>>>>>> select *
>>>>>> from TBL_Bestanden_Z oeken
>>>>>> where datum = '" & kalender.Select edDate & "'
>>>>>>
>>>>>> But when I search in the SQL Server itself, I can't do this:
>>>>>> select *
>>>>>> from TBL_Bestanden_Z oeken
>>>>>> where datum = '31/12/2005'
>>>>>>
>>>>>> The result of kalender.Select edDate 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" <guffa@guffa.co m> wrote in message
>>>>>> news:udW9Zx1bGH A.3908@TK2MSFTN GP02.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_Z oeken
>>>>>>>> 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é[/color]
>>[/color][/color]

[Mark Rae]

Re: Dates

"Frederik Vanderhaeghe" <frederikvander haeghe@gmail.co m> wrote in message
news:O0A2Xl1bGH A.4892@TK2MSFTN GP02.phx.gbl...
[color=blue]
> What can I do?[/color]

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.

[Frederik Vanderhaeghe]

Re: Dates

Hi Mark

How can I use parameterised queries??

Fré
"Mark Rae" <mark@markN-O-S-P-A-M.co.uk> wrote in message
news:eR7YsP3bGH A.536@TK2MSFTNG P02.phx.gbl...[color=blue]
> "Frederik Vanderhaeghe" <frederikvander haeghe@gmail.co m> wrote in message
> news:O0A2Xl1bGH A.4892@TK2MSFTN GP02.phx.gbl...
>[color=green]
>> What can I do?[/color]
>
> 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.
>[/color]

[Göran Andersson]

Re: Dates

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:[color=blue]
> And how do I do that??
>
> Fré
>
> "Göran Andersson" <guffa@guffa.co m> wrote in message
> news:uCXb2u2bGH A.3484@TK2MSFTN GP03.phx.gbl...[color=green]
>> 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:[color=darkred]
>>> 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.Sql Client.SqlExcep tion: 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.Sql Client.SqlDataR eader.Read() +176
>>> System.Data.Com mon.DbDataAdapt er.FillLoadData Row(SchemaMappi ng
>>> mapping) +175
>>> System.Data.Com mon.DbDataAdapt er.FillFromRead er(Object data, String
>>> srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
>>> DataColumn parentChapterCo lumn, Object parentChapterVa lue) +260
>>> System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, String
>>> srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
>>> +129
>>> System.Data.Com mon.DbDataAdapt er.FillFromComm and(Object data, Int32
>>> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
>>> CommandBehavior behavior) +304
>>> System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, Int32
>>> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
>>> CommandBehavior behavior) +77
>>> System.Data.Com mon.DbDataAdapt er.Fill(DataSet dataSet, String
>>> srcTable) +36
>>> ZoekSite.WebFor m1.SQLUitvoeren (String orderby) in
>>> D:\Inetpub\ASP\ ZoekSite\Zoekpa gina.aspx.vb:30 3
>>> ZoekSite.WebFor m1.btnzoeken_Cl ick(Object sender, EventArgs e) in
>>> D:\Inetpub\ASP\ ZoekSite\Zoekpa gina.aspx.vb:93
>>> System.Web.UI.W ebControls.Butt on.OnClick(Even tArgs e) +108
>>>
>>> System.Web.UI.W ebControls.Butt on.System.Web.U I.IPostBackEven tHandler.RaiseP ostBackEvent(St ring
>>> eventArgument) +57
>>> System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
>>> sourceControl, String eventArgument) +18
>>> System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData)
>>> +33
>>> System.Web.UI.P age.ProcessRequ estMain() +1292
>>>
>>>
>>>
>>> "Göran Andersson" <guffa@guffa.co m> wrote in message
>>> news:O98acg2bGH A.4032@TK2MSFTN GP02.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" <guffa@guffa.co m> wrote in message
>>>>> news:e3xQBL2bGH A.4900@TK2MSFTN GP02.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.Select edDate.ToString ("yyyy-MM-dd") + "'
>>>>>> ...
>>>>>>
>>>>>> Frederik Vanderhaeghe wrote:
>>>>>>> I actually use the calendar object of asp.net. So the select is:
>>>>>>> select *
>>>>>>> from TBL_Bestanden_Z oeken
>>>>>>> where datum = '" & kalender.Select edDate & "'
>>>>>>>
>>>>>>> But when I search in the SQL Server itself, I can't do this:
>>>>>>> select *
>>>>>>> from TBL_Bestanden_Z oeken
>>>>>>> where datum = '31/12/2005'
>>>>>>>
>>>>>>> The result of kalender.Select edDate 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" <guffa@guffa.co m> wrote in message
>>>>>>> news:udW9Zx1bGH A.3908@TK2MSFTN GP02.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_Z oeken
>>>>>>>>> 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é[/color][/color]
>[/color]

[Mark Rae]

Re: Dates

"Frederik Vanderhaeghe" <frederikvander haeghe@gmail.co m> wrote in message
news:uU2jnW3bGH A.1260@TK2MSFTN GP05.phx.gbl...
[color=blue]
> Hi Mark
>
> How can I use parameterised queries??[/color]

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

[Frederik Vanderhaeghe]

Re: Dates

I tried the following, in the Enterprise Manager I executed the query with
different date formats
Select *
From TBL_Bestanden_Z oeken
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_Z oeken 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" <frederikvander haeghe@gmail.co m> wrote in message
news:O0A2Xl1bGH A.4892@TK2MSFTN GP02.phx.gbl...[color=blue]
> 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_Z oeken
> 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é
>[/color]