DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
Clint
SQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>
|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> fine,
> date?
>

DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
ClintSQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> fine,
> date?
>

DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
ClintSQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I have a column in a view which is of the DATETIME datatype. This is
> fine,
> > but when I output this to MS Reporting Services it also shows the time
> > (which is always 12:00 as we are not using time as a field).
> >
> > How do I use the cast statement or another statement to have only the
> date?
> > I have read BOL without success.
> >
> > Thanks for any help provided.
> >
> > Clint
> >
> >
>

Datetime

Hi Everyone:
I have a datetime column (col7) where the output is in the format mm/yyyy.
When I execute the following sql statement, I do get the result set as 8/200
5
or 10/2004. In all double-digit month numbers I do get the right output.
However, in case of single-digit month numbers I need the output as 08/2005
and not simply 8/2005. I would appreciate if someone can help me in this
direction.
****************************************
select (cast((datepart(month ,col7))as varchar(3))+'/'+ cast((datepart(year,
col7))as varchar(4)))as col7
FROM tablea
***************************
Thanks
Sujoy Paulright( '0' + cast( datepart(month, col7) as varchar(2)) , 2 )
"sujoyp" <sujoyp@.discussions.microsoft.com> wrote in message
news:3A4AAB69-8FE4-4F88-8BFF-30BD1C386CED@.microsoft.com...
> Hi Everyone:
> I have a datetime column (col7) where the output is in the format mm/yyyy.
> When I execute the following sql statement, I do get the result set as
8/2005
> or 10/2004. In all double-digit month numbers I do get the right output.
> However, in case of single-digit month numbers I need the output as
08/2005
> and not simply 8/2005. I would appreciate if someone can help me in this
> direction.
> ****************************************
> select (cast((datepart(month ,col7))as varchar(3))+'/'+
cast((datepart(year,
> col7))as varchar(4)))as col7
> FROM tablea
> ***************************
> Thanks
> Sujoy Paul
>|||research "case". if your month is less than 10 then you'll have just 1
diget.

> Hi Everyone:
> I have a datetime column (col7) where the output is in the format mm/yyyy.
> When I execute the following sql statement, I do get the result set as
> 8/2005 or 10/2004. In all double-digit month numbers I do get the right
> output. However, in case of single-digit month numbers I need the output
> as 08/2005 and not simply 8/2005. I would appreciate if someone can help
> me in this direction.
> ****************************************
> select (cast((datepart(month ,col7))as varchar(3))+'/'+
> cast((datepart(year, col7))as varchar(4)))as col7
> FROM tablea
> ***************************
> Thanks
> Sujoy Paul
new|||Thanks. It worked.
Sujoy
"Rebecca York" wrote:

> right( '0' + cast( datepart(month, col7) as varchar(2)) , 2 )
>
> "sujoyp" <sujoyp@.discussions.microsoft.com> wrote in message
> news:3A4AAB69-8FE4-4F88-8BFF-30BD1C386CED@.microsoft.com...
> 8/2005
> 08/2005
> cast((datepart(year,
>
>|||here's the year part too.
select right(convert(varchar(10), getdate(), 103), 7)
> right( '0' + cast( datepart(month, col7) as varchar(2)) , 2 )
>
> "sujoyp" <sujoyp@.discussions.microsoft.com> wrote in message
> news:3A4AAB69-8FE4-4F88-8BFF-30BD1C386CED@.microsoft.com...
> 8/2005
> 08/2005
> cast((datepart(year,
new

Dates Help Needed

Hey Gurus
Can you give me a clue to how to do the produce the following Output from
below table.
insert into Q2 (Emp_name,Category,StartDate,EndDate)
Select 'John', 'A10', '19961001','20000807'
Union
Select'John', 'G20', '20000803','20000815'
Union
Select 'John', 'A20', '20000807','20000822'
Union
Select'John', 'G30', '20000817','20000825'
Union
Select'John', 'A30', '20000822','99991231'
I want the result to Look like
Emp_Name Category1 Category2 StartDate EndDAte
John A10 20000801 20000803
John A10 G20 20000803 20000807
John A20 G20 20000807 20000815
John A20 20000815 20000817
John A20 G30 20000817 20000822
John A30 G30 20000822 20000825
John A30 20000825 20000831Jason
What is the purpose? Can you explain why would you want this ouptut? Based
on what?
"Jason" <bornscorpio30@.yahoo.com> wrote in message
news:eV341XZSGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Hey Gurus
> Can you give me a clue to how to do the produce the following Output from
> below table.
> insert into Q2 (Emp_name,Category,StartDate,EndDate)
> Select 'John', 'A10', '19961001','20000807'
> Union
> Select'John', 'G20', '20000803','20000815'
> Union
> Select 'John', 'A20', '20000807','20000822'
> Union
> Select'John', 'G30', '20000817','20000825'
> Union
> Select'John', 'A30', '20000822','99991231'
> I want the result to Look like
> Emp_Name Category1 Category2 StartDate EndDAte
> John A10 20000801 20000803
> John A10 G20 20000803 20000807
> John A20 G20 20000807 20000815
> John A20 20000815 20000817
> John A20 G30 20000817 20000822
> John A30 G30 20000822 20000825
> John A30 20000825 20000831
>|||Cause that is the report that i need to produce.
for employees, showing what catefory they fall under dusing different time
span
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%234L3keZSGHA.5728@.tk2msftngp13.phx.gbl...
> Jason
> What is the purpose? Can you explain why would you want this ouptut?
> Based on what?
>
> "Jason" <bornscorpio30@.yahoo.com> wrote in message
> news:eV341XZSGHA.3192@.TK2MSFTNGP09.phx.gbl...
>|||I think Uri is suggesting you give both more explanation and 'details' of
your output.Around here the more info you give the better off you are.
Guys like Uri are smart and skilled but the less you make him guess details
the more apt you are for him to figure out a solution.
"Jason" <bornscorpio30@.yahoo.com> wrote in message
news:%23od2tOeSGHA.4168@.tk2msftngp13.phx.gbl...
> Cause that is the report that i need to produce.
> for employees, showing what catefory they fall under dusing different time
> span
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%234L3keZSGHA.5728@.tk2msftngp13.phx.gbl...
>|||Please post DDL and better specs. I am assuming that on any given
date, you have 1 or 2 (vague, unnamed) categories and that you know
what a Calendar table is.
CREATE TABLE Foobar
(emp_name CHAR(10) NOT NULL,
foo_cat CHAR(3) NOT NULL
CHECK (SUBSTRING (foo_cat,1,1) IN ('A', 'G')),
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK(start_date < end_date),
PRIMARY KEY (emp_name, start_date));
Pick a date range (@.my_start_date, @.my_end_date) and use this query to
get the status on every date in that range.
SELECT F1.emp_name,
MIN(F.foo_cat) AS cat_1,
MAX(F.foo_cat) AS cat_2,
C.cal_date
FROM Calendar AS C
LEFT OUTER JOIN
Foobar AS F,
ON C.cal_date BETWEEN F.start_date AND F.end_date
WHERE C,cal_date BETWEEN @.my_start_date AND @.my_end_date;
If you really need to see this in ranges instead of day by day, we can
do that but it is messy and slow.

Dates - information entered 3 months ago

Hello All,
I need to create stored procedure that will output information created 3
months after the record was created. For example: if the stored procedure was
run today or based on a date parameter I would like it to output all records
created 3 months ago to that day. There are other parameters I need,but I
think I can take care of those,
Thanks in advance.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200804/1On Apr 29, 5:22=A0pm, "Jay via SQLMonster.com" <u7124@.uwe> wrote:
> Hello All,
> I need to create stored procedure that will output information created 3
> months after the record was created. For example: if the stored procedure =was
> run today or based on a date parameter I would like it to output all recor=ds
> created 3 months ago to that day. There are other parameters I need,but I
> think I can take care of those,
> Thanks in advance.
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx=
/sql-server-reporting/200804/1
In SQL try:
SET DATEPARAM =3D DATEADD(MONTH,-3,GETDATE())
In SSRS/VB try:
=3DDateAdd(DateInterval.Month, -3, Today())
HTH
toolman

DATEPART and DATEDIFF using VARCHAR(24) Date Format

My counterdatetime field format is varchar(24) listed below.
This format cannot be changed because it's output from perfmon. How can I
change the sql query to recognize DATEPART and DATEDIFF with my
counterdatetime field in varchar(24) format.
Please help me resolve the problem.
Thank You,
select a.counterdatetime, t.countername, avg (a.countervalue)
from counterdata a (NOLOCK),
counterdetails t (NOLOCK)
where a.counterdatetime > '2005-01-20'
AND a.CounterID = t.CounterID
AND t.countername like 'Data File(s) Size (KB)'
AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
group by a.counterdatetime, t.countername
order by a.counterdatetime
Error:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
a.counterdatetime
2005-01-20 00:00:35.316
2005-01-20 00:01:35.316Joe,
You should have stored the data in the table as a datetime datatype iand not
a character. In any case try setting the dateformat and see if that helps:
SET DATEFORMAT YMD
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:55ABEE1A-2D7B-470E-9D93-AFDE09F135A6@.microsoft.com...
> My counterdatetime field format is varchar(24) listed below.
> This format cannot be changed because it's output from perfmon. How can I
> change the sql query to recognize DATEPART and DATEDIFF with my
> counterdatetime field in varchar(24) format.
> Please help me resolve the problem.
> Thank You,
> select a.counterdatetime, t.countername, avg (a.countervalue)
> from counterdata a (NOLOCK),
> counterdetails t (NOLOCK)
> where a.counterdatetime > '2005-01-20'
> AND a.CounterID = t.CounterID
> AND t.countername like 'Data File(s) Size (KB)'
> AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
> AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
> group by a.counterdatetime, t.countername
> order by a.counterdatetime
> Error:
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
> a.counterdatetime
> 2005-01-20 00:00:35.316
> 2005-01-20 00:01:35.316
>
>|||try this
convert(datetime,@.counterdatetime, 101)
Thanks,
RK
"Joe K." wrote:

> My counterdatetime field format is varchar(24) listed below.
> This format cannot be changed because it's output from perfmon. How can I
> change the sql query to recognize DATEPART and DATEDIFF with my
> counterdatetime field in varchar(24) format.
> Please help me resolve the problem.
> Thank You,
> select a.counterdatetime, t.countername, avg (a.countervalue)
> from counterdata a (NOLOCK),
> counterdetails t (NOLOCK)
> where a.counterdatetime > '2005-01-20'
> AND a.CounterID = t.CounterID
> AND t.countername like 'Data File(s) Size (KB)'
> AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
> AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
> group by a.counterdatetime, t.countername
> order by a.counterdatetime
> Error:
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
> a.counterdatetime
> 2005-01-20 00:00:35.316
> 2005-01-20 00:01:35.316
>
>

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