Datetime data type resulted in an out-of-range datetime value. Please help

Hi,

I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date

'31/08/2006 23:28:59'

I get the error "...datetime data type resulted in an out-of-range datetime value"

I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?

cheers,

Ernest

I guess itis caused by the date format in SQL Server. Please try following statements:

set DATEFORMAT dmy
declare @.t smalldatetime
set @.t='31/08/2006 23:28:59'
select @.t

|||

Thanks Lori,

It appears that when I use parameters in my SqlCommand object this works like a treat. God bless the parameters!!

DateFormat

Hi all
Whenever I use the cOnvert function to convert the date
format to other format I always get the same format of
date.
eg:
For the date '2003-12-02 09:00:00.000'
I use select convert(datetime,ShiftStartTime,126)
still getting the same output as
'2003-12-02 09:00:00.000'
but supposed to be in the format dd/mm/yy hh:mi:ss:mmmAM
as per BOL.
Please carify.
Thanks in advance
Anand.Internally, SQL stores the date with all the information it needs. You are
converting a DateTime to DateTime, if you want to display it in a certain
format, you have to use:
convert(NCHAR(23),ShiftStartTime,126)
This converts it to a string that is formatted how you want it.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Anand" <gurusanand@.yahoo.com> wrote in message
news:01f501c3d685$fa191c90$a001280a@.phx.gbl...
> Hi all
> Whenever I use the cOnvert function to convert the date
> format to other format I always get the same format of
> date.
> eg:
> For the date '2003-12-02 09:00:00.000'
> I use select convert(datetime,ShiftStartTime,126)
> still getting the same output as
> '2003-12-02 09:00:00.000'
> but supposed to be in the format dd/mm/yy hh:mi:ss:mmmAM
> as per BOL.
> Please carify.
> Thanks in advance
> Anand.
>|||The problem is that you needed to convert the date to a char or varchar, not
datetime...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anand" <gurusanand@.yahoo.com> wrote in message
news:01f501c3d685$fa191c90$a001280a@.phx.gbl...
> Hi all
> Whenever I use the cOnvert function to convert the date
> format to other format I always get the same format of
> date.
> eg:
> For the date '2003-12-02 09:00:00.000'
> I use select convert(datetime,ShiftStartTime,126)
> still getting the same output as
> '2003-12-02 09:00:00.000'
> but supposed to be in the format dd/mm/yy hh:mi:ss:mmmAM
> as per BOL.
> Please carify.
> Thanks in advance
> Anand.
>

Datediff giving output based on year...

Hi Everyone,
Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing 1 as the result. But is it possible to get the difference in year purely based on date and not only on the Year part of the date?
For Example Difference between 26 June 2002 and 21 June 2004 should give me 1 instead of 2.
Thanx in advance for the help.
Regards,
Dipankar Ganguly
Hi
Maybe something on the lines of:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT MAX([NoYears])
FROM ( SELECT 1 as [NoYears] UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) A
WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
John
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly
|||Hi,
Thanx for the opinion. Actually I want to use the datediff function only with Year parameter. And it won't be possible for me to know the year difference as hardcoded in the solution.
Regards,
Dipankar Ganguly
"John Bell" wrote:

> Hi
> Maybe something on the lines of:
> DECLARE @.StartDate Datetime
> DECLARE @.EndDate Datetime
> SET @.StartDate = '20020626'
> SET @.EndDate = '20040621'
> SELECT MAX([NoYears])
> FROM ( SELECT 1 as [NoYears] UNION ALL
> SELECT 2 UNION ALL
> SELECT 3 UNION ALL
> SELECT 4 UNION ALL
> SELECT 5 ) A
> WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> John
> "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> 1 as the result. But is it possible to get the difference in year purely
> based on date and not only on the Year part of the date?
> me 1 instead of 2.
>
>
|||Hi
Datediff will not give you the number of full years. As detailed in books
online- Datediff returns the number of date and time boundaries crossed
between two specified dates.
Try using:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT CASE WHEN MONTH(@.StartDate) > MONTH(@.EndDate) OR
(MONTH(@.StartDate) = MONTH(@.EndDate) AND DAY(@.StartDate) > DAY(@.EndDate) )
THEN YEAR(@.EndDate)-YEAR(@.StartDate) - 1
ELSE YEAR(@.EndDate)-YEAR(@.StartDate)
END AS Years
John
"dipankarganguly@.hotmail.com"
<dipankarganguly@.hotmail.com@.discussions.microsoft .com> wrote in message
news:BD2D3724-0BD4-4E9A-B69A-C9B272AF9332@.microsoft.com...
> Hi,
> Thanx for the opinion. Actually I want to use the datediff function only
with Year parameter. And it won't be possible for me to know the year
difference as hardcoded in the solution.[vbcol=seagreen]
> Regards,
> Dipankar Ganguly
> "John Bell" wrote:
showing[vbcol=seagreen]
give[vbcol=seagreen]
|||So what are you trying to accomplish? Could you post the DDL of the table or
tables you are querying?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly

Datediff giving output based on year...

Hi Everyone,
Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing 1
as the result. But is it possible to get the difference in year purely base
d on date and not only on the Year part of the date?
For Example Difference between 26 June 2002 and 21 June 2004 should give me
1 instead of 2.
Thanx in advance for the help.
Regards,
Dipankar GangulyHi
Maybe something on the lines of:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT MAX([NoYears])
FROM ( SELECT 1 as [NoYears] UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) A
WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
John
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly|||Hi,
Thanx for the opinion. Actually I want to use the datediff function only wit
h Year parameter. And it won't be possible for me to know the year differenc
e as hardcoded in the solution.
Regards,
Dipankar Ganguly
"John Bell" wrote:

> Hi
> Maybe something on the lines of:
> DECLARE @.StartDate Datetime
> DECLARE @.EndDate Datetime
> SET @.StartDate = '20020626'
> SET @.EndDate = '20040621'
> SELECT MAX([NoYears])
> FROM ( SELECT 1 as [NoYears] UNION ALL
> SELECT 2 UNION ALL
> SELECT 3 UNION ALL
> SELECT 4 UNION ALL
> SELECT 5 ) A
> WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> John
> "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> 1 as the result. But is it possible to get the difference in year purely
> based on date and not only on the Year part of the date?
> me 1 instead of 2.
>
>|||Hi
Datediff will not give you the number of full years. As detailed in books
online- Datediff returns the number of date and time boundaries crossed
between two specified dates.
Try using:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT CASE WHEN MONTH(@.StartDate) > MONTH(@.EndDate) OR
(MONTH(@.StartDate) = MONTH(@.EndDate) AND DAY(@.StartDate) > DAY(@.EndDate) )
THEN YEAR(@.EndDate)-YEAR(@.StartDate) - 1
ELSE YEAR(@.EndDate)-YEAR(@.StartDate)
END AS Years
John
"dipankarganguly@.hotmail.com"
<dipankarganguly@.hotmail.com@.discussions.microsoft.com> wrote in message
news:BD2D3724-0BD4-4E9A-B69A-C9B272AF9332@.microsoft.com...
> Hi,
> Thanx for the opinion. Actually I want to use the datediff function only
with Year parameter. And it won't be possible for me to know the year
difference as hardcoded in the solution.[vbcol=seagreen]
> Regards,
> Dipankar Ganguly
> "John Bell" wrote:
>
showing[vbcol=seagreen]
give[vbcol=seagreen]|||So what are you trying to accomplish? Could you post the DDL of the table or
tables you are querying?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly

Datediff giving output based on year...

Hi Everyone,
Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing 1 as the result. But is it possible to get the difference in year purely based on date and not only on the Year part of the date?
For Example Difference between 26 June 2002 and 21 June 2004 should give me 1 instead of 2.
Thanx in advance for the help.
Regards,
Dipankar GangulyHi
Maybe something on the lines of:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT MAX([NoYears])
FROM ( SELECT 1 as [NoYears] UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) A
WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
John
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly|||Hi,
Thanx for the opinion. Actually I want to use the datediff function only with Year parameter. And it won't be possible for me to know the year difference as hardcoded in the solution.
Regards,
Dipankar Ganguly
"John Bell" wrote:
> Hi
> Maybe something on the lines of:
> DECLARE @.StartDate Datetime
> DECLARE @.EndDate Datetime
> SET @.StartDate = '20020626'
> SET @.EndDate = '20040621'
> SELECT MAX([NoYears])
> FROM ( SELECT 1 as [NoYears] UNION ALL
> SELECT 2 UNION ALL
> SELECT 3 UNION ALL
> SELECT 4 UNION ALL
> SELECT 5 ) A
> WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> John
> "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> > Hi Everyone,
> > Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
> 1 as the result. But is it possible to get the difference in year purely
> based on date and not only on the Year part of the date?
> > For Example Difference between 26 June 2002 and 21 June 2004 should give
> me 1 instead of 2.
> > Thanx in advance for the help.
> >
> > Regards,
> > Dipankar Ganguly
>
>|||Hi
Datediff will not give you the number of full years. As detailed in books
online- Datediff returns the number of date and time boundaries crossed
between two specified dates.
Try using:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT CASE WHEN MONTH(@.StartDate) > MONTH(@.EndDate) OR
(MONTH(@.StartDate) = MONTH(@.EndDate) AND DAY(@.StartDate) > DAY(@.EndDate) )
THEN YEAR(@.EndDate)-YEAR(@.StartDate) - 1
ELSE YEAR(@.EndDate)-YEAR(@.StartDate)
END AS Years
John
"dipankarganguly@.hotmail.com"
<dipankarganguly@.hotmail.com@.discussions.microsoft.com> wrote in message
news:BD2D3724-0BD4-4E9A-B69A-C9B272AF9332@.microsoft.com...
> Hi,
> Thanx for the opinion. Actually I want to use the datediff function only
with Year parameter. And it won't be possible for me to know the year
difference as hardcoded in the solution.
> Regards,
> Dipankar Ganguly
> "John Bell" wrote:
> > Hi
> >
> > Maybe something on the lines of:
> >
> > DECLARE @.StartDate Datetime
> > DECLARE @.EndDate Datetime
> >
> > SET @.StartDate = '20020626'
> > SET @.EndDate = '20040621'
> > SELECT MAX([NoYears])
> > FROM ( SELECT 1 as [NoYears] UNION ALL
> > SELECT 2 UNION ALL
> > SELECT 3 UNION ALL
> > SELECT 4 UNION ALL
> > SELECT 5 ) A
> > WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> >
> > John
> >
> > "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> > message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> > > Hi Everyone,
> > > Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is
showing
> > 1 as the result. But is it possible to get the difference in year purely
> > based on date and not only on the Year part of the date?
> > > For Example Difference between 26 June 2002 and 21 June 2004 should
give
> > me 1 instead of 2.
> > > Thanx in advance for the help.
> > >
> > > Regards,
> > > Dipankar Ganguly
> >
> >
> >|||So what are you trying to accomplish? Could you post the DDL of the table or
tables you are querying?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly