lhaluska
asked on
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
SELECT COUNT(SRDATETIMESTAMP) AS Total, DATEDIFF(month, - 6 > getdate(), getdate()) AS no_of_days
FROM dbo.caseData
ASKER
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
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
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
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
ASKER
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...:)
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...:)
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
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_
[runid] [int] NULL,
[command] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[params] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_
[message] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_
[status_code] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_
CONSTRAINT [PK_AUDIT] PRIMARY KEY CLUSTERED
(
[audit_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Regards
David
ASKER
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
[caseID] [int] IDENTITY (1, 1) NOT NULL ,
[customerPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[customerEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[CaseState] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[dateNeeded] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[SysName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[SysLoc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[SysType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[OwnDept] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[SRDATETIMESTAMP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[problemDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_
[chowned] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[routedTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[Owner] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[ReceiveMail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[totalOnHOldTIme] [int] NULL ,
[submitTime] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[SubmitAdjustments] [int] NULL ,
[SRQuarter] [datetime] NULL ,
[Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[custMang] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_
[subType] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_
CONSTRAINT [PK_caseData] PRIMARY KEY CLUSTERED
(
[caseID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Try this
SELECT COUNT( caseData.SRDATETIMESTAMP ) AS Total
FROM dbo.caseData
where CAST(caseData.SRDATETIMEST AMP as datetime) > DATEDIFF(month, - 6, getdate())
SELECT COUNT( caseData.SRDATETIMESTAMP ) AS Total
FROM dbo.caseData
where CAST(caseData.SRDATETIMEST
ASKER
I tried the last query and I am getting stuff that is for the 6 months but for last year too.
Youn can add more codition
SELECT COUNT( caseData.SRDATETIMESTAMP ) AS Total
FROM dbo.caseData
where CAST(caseData.SRDATETIMEST AMP as datetime) > DATEDIFF(month, - 6, getdate()) AND
YEAR(CAST(caseData.SRDATET IMESTAMP as datetime)) = YEAR(getdate())
SELECT COUNT( caseData.SRDATETIMESTAMP ) AS Total
FROM dbo.caseData
where CAST(caseData.SRDATETIMEST
YEAR(CAST(caseData.SRDATET
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
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
Sorry, I have missing
SELECT COUNT( caseData.SRDATETIMESTAMP ) AS Total
FROM dbo.caseData
where CAST(caseData.SRDATETIMEST AMP as datetime) > DATEADD(month, - 6, getdate()) AND
YEAR(CAST(caseData.SRDATET IMESTAMP as datetime)) = YEAR(getdate())
SELECT COUNT( caseData.SRDATETIMESTAMP ) AS Total
FROM dbo.caseData
where CAST(caseData.SRDATETIMEST
YEAR(CAST(caseData.SRDATET
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
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
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)
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)
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,SRDATETIMESTAM P,Getdate( ))<=180 --- if you want to go back 180 days or
Where DateDiff(mm,SRDATETIMESTAM P,Getdate( ))<=6--- if you want to go back 6 months
With DateAdd all you need is following in your where clause
Where SRDATETIMESTAMP>=DateAdd(d d,-180,Get date())-- if you want to go back 180 days or
Where SRDATETIMESTAMP>=DateAdd(m m,-6,Getda te())-- 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 :)
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,SRDATETIMESTAM
Where DateDiff(mm,SRDATETIMESTAM
With DateAdd all you need is following in your where clause
Where SRDATETIMESTAMP>=DateAdd(d
Where SRDATETIMESTAMP>=DateAdd(m
you can also try any of Aaron's solution, I dont think there is any thing dirty about that as Aaron pointed :)
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