Datetime Issues

Hi,
I have an IIS query (using VB) that selects information
from a table within SQL Server 2000. When the function is
called, it gives error message "Microsoft OLE DB Provider
for SQL Server error '80040e07'". A Datetime error.
The line that fails is:
SELECT Name, Description, Reason, StartDate, EndDate,
Forename, Surname FROM V_GetPartnerships WHERE
PartnershipId = 2 AND (StartDate <= 'Wednesday, October
29, 2003' AND EndDate >= 'Wednesday, October 29, 2003')
ORDER BY name;
I have run this same line successfully by omitting
the "Wednesday, " section from the date fields, because
it does not fit in with the datetime defaults.
I have checked the outputs of the date formats using a
simple VB script, and they are as expected (i.e. October
29, 2003).
Where is it getting this extended date from'Assuming Startdate and Enddate are DATETIME or SMALLDATETIME columns, try
this:
SELECT [name], [description], reason, startdate, enddate,
forename, surname
FROM V_GetPartnerships
WHERE partnershipid = 2
AND '20031029' BETWEEN startdate AND enddate
ORDER BY name
Always specify dates in the ISO format ('YYYYMMDD') which works
independently of any regional settings.
--
David Portas
--
Please reply only to the newsgroup
--|||This is an SQL database and IIS that previously worked
OK, but has been migrated to new servers.
The date and time selections it is using determines the
query (by the use of VB Combo boxes within the website)
and any changes to this would require extensive re-
writing of the VB codes.
The datetime field works OK when using correct date
terms, as previously tested, it is only when it using
this extended 'Wednesday, October 29, 2003' that is NOT
recognised by the field.
Any ideas where it would be getting this time field from?
I.E. IIS or SQL Server properties etc.
Or is it possible to update SQL to allow for the day,
month date, year datetime fields.
>--Original Message--
>Assuming Startdate and Enddate are DATETIME or
SMALLDATETIME columns, try
>this:
>SELECT [name], [description], reason, startdate, enddate,
> forename, surname
> FROM V_GetPartnerships
> WHERE partnershipid = 2
> AND '20031029' BETWEEN startdate AND enddate
> ORDER BY name
>Always specify dates in the ISO format ('YYYYMMDD')
which works
>independently of any regional settings.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||SQL Server does not understand below "extended" format. Take the time to do it right, use the format
recommended by David, and you will not run into all kind of problems later. Or read about the
supported formats in Books Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_765d.asp?frame=true
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Martin Powell" <anonymous@.discussions.microsoft.com> wrote in message
news:0c4b01c39e29$26996260$a501280a@.phx.gbl...
> This is an SQL database and IIS that previously worked
> OK, but has been migrated to new servers.
> The date and time selections it is using determines the
> query (by the use of VB Combo boxes within the website)
> and any changes to this would require extensive re-
> writing of the VB codes.
> The datetime field works OK when using correct date
> terms, as previously tested, it is only when it using
> this extended 'Wednesday, October 29, 2003' that is NOT
> recognised by the field.
> Any ideas where it would be getting this time field from?
> I.E. IIS or SQL Server properties etc.
> Or is it possible to update SQL to allow for the day,
> month date, year datetime fields.
> >--Original Message--
> >Assuming Startdate and Enddate are DATETIME or
> SMALLDATETIME columns, try
> >this:
> >
> >SELECT [name], [description], reason, startdate, enddate,
> > forename, surname
> > FROM V_GetPartnerships
> > WHERE partnershipid = 2
> > AND '20031029' BETWEEN startdate AND enddate
> > ORDER BY name
> >
> >Always specify dates in the ISO format ('YYYYMMDD')
> which works
> >independently of any regional settings.
> >
> >--
> >David Portas
> >--
> >Please reply only to the newsgroup
> >--
> >
> >
> >.
> >|||I am aware that SQL does not support the format that is
in use, the issue is that it is getting this format from
somewhere within the system.
This database has been developed over several years and
recently migrated to new servers and this error has only
appears on the new system. It DOES NOT affect the old
system.
Any ideas where it would be getting this time field from?
>--Original Message--
>SQL Server does not understand below "extended" format.
Take the time to do it right, use the format
>recommended by David, and you will not run into all kind
of problems later. Or read about the
>supported formats in Books Online:
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/acdata/ac_8_con_03_765d.asp?frame=true
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Martin Powell" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0c4b01c39e29$26996260$a501280a@.phx.gbl...
>> This is an SQL database and IIS that previously worked
>> OK, but has been migrated to new servers.
>> The date and time selections it is using determines the
>> query (by the use of VB Combo boxes within the website)
>> and any changes to this would require extensive re-
>> writing of the VB codes.
>> The datetime field works OK when using correct date
>> terms, as previously tested, it is only when it using
>> this extended 'Wednesday, October 29, 2003' that is NOT
>> recognised by the field.
>> Any ideas where it would be getting this time field
from?
>> I.E. IIS or SQL Server properties etc.
>> Or is it possible to update SQL to allow for the day,
>> month date, year datetime fields.
>> >--Original Message--
>> >Assuming Startdate and Enddate are DATETIME or
>> SMALLDATETIME columns, try
>> >this:
>> >
>> >SELECT [name], [description], reason, startdate,
enddate,
>> > forename, surname
>> > FROM V_GetPartnerships
>> > WHERE partnershipid = 2
>> > AND '20031029' BETWEEN startdate AND enddate
>> > ORDER BY name
>> >
>> >Always specify dates in the ISO format ('YYYYMMDD')
>> which works
>> >independently of any regional settings.
>> >
>> >--
>> >David Portas
>> >--
>> >Please reply only to the newsgroup
>> >--
>> >
>> >
>> >.
>> >
>
>.
>|||Ahh, I see. I'm no ASP expert, but this is where you have to look (in the application code).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:062801c39e2c$1c858d50$a001280a@.phx.gbl...
> I am aware that SQL does not support the format that is
> in use, the issue is that it is getting this format from
> somewhere within the system.
> This database has been developed over several years and
> recently migrated to new servers and this error has only
> appears on the new system. It DOES NOT affect the old
> system.
> Any ideas where it would be getting this time field from?
> >--Original Message--
> >SQL Server does not understand below "extended" format.
> Take the time to do it right, use the format
> >recommended by David, and you will not run into all kind
> of problems later. Or read about the
> >supported formats in Books Online:
> >http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/acdata/ac_8_con_03_765d.asp?frame=true
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Martin Powell" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:0c4b01c39e29$26996260$a501280a@.phx.gbl...
> >> This is an SQL database and IIS that previously worked
> >> OK, but has been migrated to new servers.
> >>
> >> The date and time selections it is using determines the
> >> query (by the use of VB Combo boxes within the website)
> >> and any changes to this would require extensive re-
> >> writing of the VB codes.
> >>
> >> The datetime field works OK when using correct date
> >> terms, as previously tested, it is only when it using
> >> this extended 'Wednesday, October 29, 2003' that is NOT
> >> recognised by the field.
> >>
> >> Any ideas where it would be getting this time field
> from?
> >> I.E. IIS or SQL Server properties etc.
> >>
> >> Or is it possible to update SQL to allow for the day,
> >> month date, year datetime fields.
> >>
> >> >--Original Message--
> >> >Assuming Startdate and Enddate are DATETIME or
> >> SMALLDATETIME columns, try
> >> >this:
> >> >
> >> >SELECT [name], [description], reason, startdate,
> enddate,
> >> > forename, surname
> >> > FROM V_GetPartnerships
> >> > WHERE partnershipid = 2
> >> > AND '20031029' BETWEEN startdate AND enddate
> >> > ORDER BY name
> >> >
> >> >Always specify dates in the ISO format ('YYYYMMDD')
> >> which works
> >> >independently of any regional settings.
> >> >
> >> >--
> >> >David Portas
> >> >--
> >> >Please reply only to the newsgroup
> >> >--
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||If you are asking why IIS or VB is formatting the date this way then I
really don't know. Presumably it's defined by a regional setting on the
server - it looks like a Windows "Long" date format. This isn't a SQLServer
question so you'll probably get more help posting to an IIS group.
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks Guys,
Will try with IIS, now SQL has been ruled out!
>--Original Message--
>If you are asking why IIS or VB is formatting the date
this way then I
>really don't know. Presumably it's defined by a regional
setting on the
>server - it looks like a Windows "Long" date format.
This isn't a SQLServer
>question so you'll probably get more help posting to an
IIS group.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

Dates overlow

Dear All,
When I enter a date with the year before 1950, it is rejected with an
"overflow" message.
I'm building an application that uses dates that ranges from 1200 till 2004.
So, does any body know how to solve this problem?
Regards,
Mohamed El Wakil
Teaching Assistant
Information Systems Department,
Faculty of Computers and Information,
Cairo University - Cairo
http://mohamedelwakil.tripod.com
Please, reply to mohamed.elwakil@.omeldonia.com
SQL Server accepts date from 1753 to 9999 for the datetime datatype. If you need to go out of this
span, you need to use some other representation. A string representation is one option. A number of
integer columns (one for each element) is another. One integer column counting seconds from a
certain reference date is yet another option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till 2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>
|||The exact "overflow" error would be helpful. Does it come from SQL Server
or from whatever application you are using to enter the date?
Are you using two digits for the year? I recommend that you use more...
There are easier ways to enter dates than typing them all in...a WHILE loop
might be easier!
You may have some problems entering dates earlier than 1753.
From Books Online:
datetime and smalldatetime
Date and time data types for representing date and time of day.
datetime
Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table.
Keith
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till
2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>
|||Date outside the range 1753-9999 is not accepted at datetime type. Look for
datetime and smalldatetime in BOL for more detail.
You can come up with your own definition, not as datetime, but such as a
char column, to accommodate your needs.
Quentin
"Mohamed Medhat" <mohamed.elwakil @. omeldonia.com> wrote in message
news:3C5F569B-9C67-421C-802E-24D14B301044@.microsoft.com...
> Dear All,
> When I enter a date with the year before 1950, it is rejected with an
> "overflow" message.
> I'm building an application that uses dates that ranges from 1200 till
2004.
> So, does any body know how to solve this problem?
> Regards,
> Mohamed El Wakil
> Teaching Assistant
> Information Systems Department,
> Faculty of Computers and Information,
> Cairo University - Cairo
> http://mohamedelwakil.tripod.com
>
> Please, reply to mohamed.elwakil@.omeldonia.com
>

Dates not working in SQL query

This is the query that gives the error message for 'Oct 9, 2003' on a server
but works correctly on another - what could make this happen on SQL 2000
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
SELECT count(*)
FROM Contact INNER JOIN
OrderHeader ON Contact.ContactID = OrderHeader.ContactID INNER JOIN
PledgeDetails ON OrderHeader.OrderID = PledgeDetails.OrderID
WHERE (PledgeDetails.LastPaymentDate >= 'Oct 9, 2003') AND
(PledgeDetails.AutoDeduction = 1) AND (PledgeDetails.NextDueDate >= 'Jan
1, 1980') AND
(PledgeDetails.NextDueDate <= 'Oct 9, 2003') AND
(LEFT(OrderHeader.OrderCode, 1) = '1')
AND (Contact.flag9 = 0 OR Contact.flag9 IS NULL) AND (Contact.flag10 = 0
OR Contact.flag10 IS NULL) AND (Contact.flag15 = 0 OR Contact.flag15 IS
NULL)
Regards Jeff
This is the query that gives the error message for 'Oct 9, 2003' on a server
but works correctly on another - what could make this happen on SQL 2000
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
SELECT count(*)
FROM Contact INNER JOIN
OrderHeader ON Contact.ContactID = OrderHeader.ContactID INNER JOIN
PledgeDetails ON OrderHeader.OrderID = PledgeDetails.OrderID
WHERE (PledgeDetails.LastPaymentDate >= 'Oct 9, 2003') AND
(PledgeDetails.AutoDeduction = 1) AND (PledgeDetails.NextDueDate >= 'Jan
1, 1980') AND
(PledgeDetails.NextDueDate <= 'Oct 9, 2003') AND
(LEFT(OrderHeader.OrderCode, 1) = '1')
AND (Contact.flag9 = 0 OR Contact.flag9 IS NULL) AND (Contact.flag10 = 0
OR Contact.flag10 IS NULL) AND (Contact.flag15 = 0 OR Contact.flag15 IS
NULL)
Regards Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.524 / Virus Database: 321 - Release Date: 6/10/2003Jeff
Please refer to BOL for CONVERT function.
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:#AtCV9SjDHA.1672@.TK2MSFTNGP09.phx.gbl...
> This is the query that gives the error message for 'Oct 9, 2003' on a
server
> but works correctly on another - what could make this happen on SQL 2000
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
>
> SELECT count(*)
> FROM Contact INNER JOIN
> OrderHeader ON Contact.ContactID = OrderHeader.ContactID INNER JOIN
> PledgeDetails ON OrderHeader.OrderID = PledgeDetails.OrderID
> WHERE (PledgeDetails.LastPaymentDate >= 'Oct 9, 2003') AND
> (PledgeDetails.AutoDeduction = 1) AND (PledgeDetails.NextDueDate >= 'Jan
> 1, 1980') AND
> (PledgeDetails.NextDueDate <= 'Oct 9, 2003') AND
> (LEFT(OrderHeader.OrderCode, 1) = '1')
> AND (Contact.flag9 = 0 OR Contact.flag9 IS NULL) AND (Contact.flag10 = 0
> OR Contact.flag10 IS NULL) AND (Contact.flag15 = 0 OR Contact.flag15 IS
> NULL)
> Regards Jeff
>
> This is the query that gives the error message for 'Oct 9, 2003' on a
server
> but works correctly on another - what could make this happen on SQL 2000
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
>
> SELECT count(*)
> FROM Contact INNER JOIN
> OrderHeader ON Contact.ContactID = OrderHeader.ContactID INNER JOIN
> PledgeDetails ON OrderHeader.OrderID = PledgeDetails.OrderID
> WHERE (PledgeDetails.LastPaymentDate >= 'Oct 9, 2003') AND
> (PledgeDetails.AutoDeduction = 1) AND (PledgeDetails.NextDueDate >= 'Jan
> 1, 1980') AND
> (PledgeDetails.NextDueDate <= 'Oct 9, 2003') AND
> (LEFT(OrderHeader.OrderCode, 1) = '1')
> AND (Contact.flag9 = 0 OR Contact.flag9 IS NULL) AND (Contact.flag10 = 0
> OR Contact.flag10 IS NULL) AND (Contact.flag15 = 0 OR Contact.flag15 IS
> NULL)
> Regards Jeff
>
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.524 / Virus Database: 321 - Release Date: 6/10/2003
>

Dates Error

Hi:

I got the next problem, when I try to modify a record of my SQL Server Database from my Delphi application the next message error appears

"Date is less than 01/12/2003"

The record that I'm trying to modify was inserted from the same applicaition.

I'm not so sure if it's a database problem, but I don't know why it is passing. What can I do?

Thaks for your help!!

Cristopher SerratoNope,

Probably someone wrote a trigger to check to rows modified date. Someone probably updated the row since you got it last..

Your update has to supply a date if I'm not mistaken...

They basically want you to requery the data so you can work with th most current version of data...

Just a guess...

datepart function not working

when i put this in a text box, i get this error message:
="Includes Date:" & DatePart(""d"", Now() )
BC30518 Overload resolution failed because no accessible 'DatePart' can be
called with these argumentsdatepart is an SQL Server function, not a Reporting services function.
try this:
now().ToString("d")
write your RS function as VB.Net functions.
"Cindy Lee" <cindylee@.hotmail.com> a écrit dans le message de
news:O4KxH7%23gEHA.3988@.tk2msftngp13.phx.gbl...
> when i put this in a text box, i get this error message:
> ="Includes Date:" & DatePart(""d"", Now() )
>
> BC30518 Overload resolution failed because no accessible 'DatePart' can be
> called with these arguments
>
>|||SQL Server Reporting Services 2000 supports a large number of VB .NET
functions including the date manipulation ones.
Included in this set a DatePart() function. Pleaser refer to MSDN for more
information.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:ub$Jif$gEHA.1972@.TK2MSFTNGP09.phx.gbl...
> datepart is an SQL Server function, not a Reporting services function.
> try this:
> now().ToString("d")
> write your RS function as VB.Net functions.
> "Cindy Lee" <cindylee@.hotmail.com> a écrit dans le message de
> news:O4KxH7%23gEHA.3988@.tk2msftngp13.phx.gbl...
> > when i put this in a text box, i get this error message:
> >
> > ="Includes Date:" & DatePart(""d"", Now() )
> >
> >
> >
> > BC30518 Overload resolution failed because no accessible 'DatePart' can
be
> > called with these arguments
> >
> >
> >
>

Dateadd and Dynamic SQL

Hi!
I am trying to pass two variables @.date and @.days in the DATEADD
function but getting syntax error message saying "Syntax error
converting the varchar value 'select dateadd(day, ' to a column of data
type int.". I think I am not using the correct syntax. Can you please
help in correcting it?
Thanks,
declare @.date datetime
declare @.days int
set @.date = '5/19/2005'
set @.days = 60
declare @.S varchar(100)
-- hard coded works
--set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
+ ''')'
set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
convert(varchar(10), @.date, 110) + ''')'
print @.S
*** Sent via Developersdex http://www.examnotes.net ***Any reason you are doing dynamic SQL
Anyway the code below works
declare @.date datetime
declare @.days int
set @.date = '5/19/2005'
set @.days = 60
declare @.S varchar(100)
select @.S = dateadd(day, @.days ,convert(varchar(10), @.date, 110) )
print @.S
http://sqlservercode.blogspot.com/
"Test Test" wrote:

> Hi!
> I am trying to pass two variables @.date and @.days in the DATEADD
> function but getting syntax error message saying "Syntax error
> converting the varchar value 'select dateadd(day, ' to a column of data
> type int.". I think I am not using the correct syntax. Can you please
> help in correcting it?
> Thanks,
> declare @.date datetime
> declare @.days int
> set @.date = '5/19/2005'
> set @.days = 60
> declare @.S varchar(100)
> -- hard coded works
> --set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
> + ''')'
> set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
> convert(varchar(10), @.date, 110) + ''')'
> print @.S
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Yes I need it to be done using dynamic SQL bc. Thanks!
*** Sent via Developersdex http://www.examnotes.net ***|||Try,
set @.S = 'select dateadd(day, ' + ltrim(@.days) + ', @.date)'
AMB
"Test Test" wrote:

> Hi!
> I am trying to pass two variables @.date and @.days in the DATEADD
> function but getting syntax error message saying "Syntax error
> converting the varchar value 'select dateadd(day, ' to a column of data
> type int.". I think I am not using the correct syntax. Can you please
> help in correcting it?
> Thanks,
> declare @.date datetime
> declare @.days int
> set @.date = '5/19/2005'
> set @.days = 60
> declare @.S varchar(100)
> -- hard coded works
> --set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
> + ''')'
> set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
> convert(varchar(10), @.date, 110) + ''')'
> print @.S
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Correction,
set @.S = 'select dateadd(day, ' + ltrim(@.days) + ',''' +
convert(varchar(35), @.date, 126) + ''')'
AMB
"Alejandro Mesa" wrote:
> Try,
> set @.S = 'select dateadd(day, ' + ltrim(@.days) + ', @.date)'
>
> AMB
>
> "Test Test" wrote:
>