6 month query and count

I have a query that counts the number of recorders and it is supposed to be able to select records for 6 month from the current date but it has problem not sure why HELP PLEASE... Here is the query...

SELECT     COUNT(SRDATETIMESTAMP) AS Total, DATEDIFF(month, - 6 > getdate(), getdate()) AS no_of_days
FROM         dbo.caseData
LVL 1
lhaluskaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

Try

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   caseData.TransDate > DATEDIFF(month, - 6,  getdate())

I'm guesing was the date field in your table is though ...

Regards
  David
0
lhaluskaAuthor Commented:
I am sorry my date field is :  SRDATETIMESTAMP

here is the new query

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   caseData.TransDate > DATEDIFF(month, - 6,  getdate())

I am getting a nvarchar to int error here is what the SRDATETIMESTAMP looks like..
October 06, 2005 09:10
0
David ToddSenior DBACommented:
Hi

Try
SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   caseData.SRDATETIMESTAMP > DATEDIFF(month, - 6,  getdate())

Can you post the table schema? In Query Analyzer, right click on the table in the object browser window, and select script to clipboard ...

Regards
  David
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lhaluskaAuthor Commented:
here is what the error looks like

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value 'April 03, 2006 06:00' to a column of data type int.

Here is the output for the script...

SELECT [caseID], [customerPhone], [customerEmail], [CaseState], [dateNeeded], [SysName], [SysLoc], [SysType], [OwnDept], [SRDATETIMESTAMP], [problemDescription], [chowned], [routedTo], [Owner], [ReceiveMail], [totalOnHOldTIme], [submitTime], [SubmitAdjustments], [SRQuarter], [Region], [custMang], [subType] FROM [NSKInfo].[dbo].[caseData]


Thank you very much so far...:)
0
David ToddSenior DBACommented:
Hi,

Can you pick up the script table as Create rather than a select.

What I'm after are the column types, as it appears that the datetime is not a datetime but characters. But I don't know which way round the problem is with out the full create sciprt.

For instance, picking a table at random from our server
USE [databasename]
GO
/****** Object:  Table [dbo].[audit]    Script Date: 11/03/2006 14:17:30 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[audit](
      [audit_id] [int] IDENTITY(1,1) NOT NULL,
      [process_date] [datetime] NOT NULL,
      [start_datetime] [datetime] NOT NULL,
      [end_datetime] [datetime] NULL,
      [type] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [runid] [int] NULL,
      [command] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [params] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [message] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [status_code] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_AUDIT] PRIMARY KEY CLUSTERED
(
      [audit_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Regards
  David
0
lhaluskaAuthor Commented:
CREATE TABLE [caseData] (
      [caseID] [int] IDENTITY (1, 1) NOT NULL ,
      [customerPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [customerEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CaseState] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [dateNeeded] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SysName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SysLoc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SysType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [OwnDept] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SRDATETIMESTAMP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [problemDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [chowned] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [routedTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Owner] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ReceiveMail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [totalOnHOldTIme] [int] NULL ,
      [submitTime] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SubmitAdjustments] [int] NULL ,
      [SRQuarter] [datetime] NULL ,
      [Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [custMang] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [subType] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      CONSTRAINT [PK_caseData] PRIMARY KEY  CLUSTERED
      (
            [caseID]
      )  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


0
HuyBDCommented:
Try this

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   CAST(caseData.SRDATETIMESTAMP as datetime) > DATEDIFF(month, - 6,  getdate())
0
lhaluskaAuthor Commented:
I tried the last query and I am getting stuff that is for the 6 months but for last year too.
0
HuyBDCommented:
Youn can add more codition

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   CAST(caseData.SRDATETIMESTAMP as datetime) > DATEDIFF(month, - 6,  getdate()) AND
YEAR(CAST(caseData.SRDATETIMESTAMP as datetime)) = YEAR(getdate())
0
David ToddSenior DBACommented:
Hi,

datediff should in fact be dateadd.

The cast should be using a convert and the style parameter for the date.

Try
SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   convert( datetime, caseData.SRDATETIMESTAMP, 107 ) > dateadd(month, - 6,  getdate())

if your six months includes last year, but you don't want last year (for example in may, six months includes the previous december)

then add
and  convert( datetime, caseData.SRDATETIMESTAMP, 107 )  > dateadd( year, datediff( year, 0 , getdate() ) - 1, 0 )

Regards
  David
0
HuyBDCommented:
Sorry, I have missing

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   CAST(caseData.SRDATETIMESTAMP as datetime) > DATEADD(month, - 6,  getdate()) AND
YEAR(CAST(caseData.SRDATETIMESTAMP as datetime)) = YEAR(getdate())
0
David ToddSenior DBACommented:
Hi,

Try

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   convert( datetime, caseData.SRDATETIMESTAMP, 107 ) > dateadd(month, - 6,  getdate())
and  convert( datetime, caseData.SRDATETIMESTAMP, 107 )  > dateadd( year, datediff( year, 0 , getdate() ) - 1, 0 )


Sorry. Should have picked up the datediff thing before.

Regards
  David
0
AaronAbendCommented:
for a quick and dirty approach, try this...

SELECT     COUNT(SRDATETIMESTAMP) AS Total
FROM         dbo.caseData
where SRDATETIMESTAMP >getdate()-180

for SRDATETIMESTAMP's that are greater than the last 6 months (assuming none can be later than today)

or for a bounded range...

SELECT     COUNT(SRDATETIMESTAMP) AS Total
FROM         dbo.caseData
where SRDATETIMESTAMP between getdate()-180 and getdate()


your question did not make clear whether you wanted the past 6 months or the next 6 months... for the future 6 month period...


SELECT     COUNT(SRDATETIMESTAMP) AS Total
FROM         dbo.caseData
where SRDATETIMESTAMP between getdate() and getdate()+180

6 months is a vague concept... I am using 180 days as a surrogate (you could use 182.5 to make it a "half year" - also an inexact value because of leapyears)


0
David ToddSenior DBACommented:
Hi,

Based on Aaron's work, think about this idea - not often do you want the split months. You'll want the last six complete months.

select count( caseData.srdatetimestamp ) as total
from dbo.caseData
where datediff( month, 0, caseData.srdatetimestamp )
    between datediff( month, 0 , getdate() ) - 6 and datediff( month, 0, getdate() )

Regards
  David
0
David ToddSenior DBACommented:
Hi,

Okay, try again.

select count( caseData.srdatetimestamp ) as total
from dbo.caseData
where
      datediff( month, 0, convert( datetime, caseData.SRDATETIMESTAMP, 107 ) ) >= datediff( month, 0 , getdate() ) - 6
    and datediff( month, 0, convert( datetime, caseData.SRDATETIMESTAMP, 107 ) ) < datediff( month, 0, getdate() )

The between above will add the current month data in here.

To illustrate the datetime manipulation, try this code
select getdate()

select datediff( month, 0, getdate() )

select datediff( month, 0 , getdate() ) - 6

select dateadd( month, datediff( month, 0, getdate() ), 0 )

select dateadd( month, datediff( month, 0 , getdate() ) - 6, 0 )

Regards
  David
0
satish_nagdevCommented:
hi,
could you try this please
--i guess you've some primary key field
declare @dt varchar
select getdate()
set @dt = cast(year(dateadd(mm,-6,getdate())) as varchar) +'-' + cast(datepart(mm,dateadd(mm,-6,getdate())) as varchar)+'-'+'01-' //get first date of six months back, you could take exact date using datepart six months back like 3rd march in today's case
--select @dt

SELECT     COUNT( PID )  AS Total, *
FROM         dbo.caseData
where   convert(varchar,caseData.TransDate,103) between @dt and convert(varchar, DATEDIFF(month, - 6,  getdate())

hope it helps,

regards,
satish.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rw3adminCommented:
I am sorry I jumping although this post is already crowded, I am though surprised that datediff was continiously used in wrong way in nearly all examples and I am sure you had your DateDiff and DateAdd confused

here is what BOL says about datediff

DATEDIFF (T-SQL)
Returns the number of date and time boundaries crossed between two specified dates.
Syntax
DATEDIFF(datepart, startdate, enddate)

and about dateadd
DATEADD (T-SQL)
Returns a new datetime value based on adding an interval to the specified date.
Syntax
DATEADD(datepart, number, date)


With DateDiff all you need was in your where clause
Where DateDiff(dd,SRDATETIMESTAMP,Getdate())<=180 --- if you want to go back 180 days or
Where DateDiff(mm,SRDATETIMESTAMP,Getdate())<=6--- if you want to go back 6 months

With DateAdd all you need is following in your where clause
Where SRDATETIMESTAMP>=DateAdd(dd,-180,Getdate())-- if you want to go back 180 days or
Where SRDATETIMESTAMP>=DateAdd(mm,-6,Getdate())-- if you want to go back 6 months

you can also try any of Aaron's solution, I dont think there is any thing dirty about that as Aaron pointed :)

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.