How do I exclude showing the seconds out of a datetime column?
I tried converting it to smalldatetime but it only rounds it up to "00".
e.g.
2004-01-03 16:33:20
I want to show only:
2004-01-03 16:33
I know this can be achive by using Datepart function calling each
part of the datetime, but is there a simplier way?
Basically the datetime value will be shown to a ASP page. And I don' t want to show until seconds.Have you tried to cast/convert it to a varchar ?|||ooh...thanks...,
err...what if I want it to remain showing numbers instead of the date names?
Cast/convert will change the datetime column into date names
e.g. JAN,FEB...etc....|||Like ...
convert(varchar(16),datecol,121)|||Thanks..that was a fast response.|||When you need to change the format of the date - look in bol under "Cast and Convert", it will show you the formats that enigma references.
Showing posts with label showing. Show all posts
Showing posts with label showing. Show all posts
Monday, March 19, 2012
Saturday, February 25, 2012
Dates get alphabetized when report is shown
I've built a report from a cube that I have had made. After selecting a few dimensions, the columns will be showing a drill down action related to different dates. Problem is, when you preview the report, the dates get alphabetized; they don't show up in an order like dates, days should.
ex: monday, friday, thursday, tuesday, wednesday
or april, august, july, june, may
How can this be changed, or is it related to the dimensions in the way they were made? Possibly something from the tables then? If more information is needed, please specify.
Im running Sql 2005 Developer Edition, with BIDS.
Sounds like your dates are set up with a string data type so you'll want to change that in the database/cube or you could try a cdate() in reporting services to convert it to a date (e.g. cdate(Fields!Month.value, "MMM"). But if you're going to use the cube alot (and the dates) it would be better to alter it in the back end.
Regards,
Ali
|||Do the dates come back in that order in the query designer? If so, you need to go back to the cube, and change your dimensions to order properly. You can do that by using the OrderBy property on the dimension attribute. If you are using a numeric key that has the proper order (1 for Quarter 1, 2 for Quarter 2, etc.), you can order by the key. If not, you can add a related attribute that contians the proper sort order, or you may need to redesign the dimension somewhat.|||I guess you are using a matrix. Assuming that, set the sorting of the corresponding groups in the matrix to sort by the date field itself instead of the weekday name or month name.
Shyam
|||
Shyam Sundar wrote:
I guess you are using a matrix. Assuming that, set the sorting of the corresponding groups in the matrix to sort by the date field itself instead of the weekday name or month name.
Shyam
yes I am using a matrix, but Im not sure how to do this.
I am new to sql 2005, and all of these suggestions thus far sound good. I will be on the phone with these specifics to my associate who made the cube. I'll need to have those dimensions tweaked some more. Thanks!
I'll report back as to what the issue turned out to be.
|||Right click on the appropriate row group cell and click on Edit Group. Go to Sorting tab and select the date field under Expression and select Ascending under Direction.
Shyam
Friday, February 17, 2012
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
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
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
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
Subscribe to:
Posts (Atom)