date queries

I have written a report that contains multiple queries that displays the
number of applications we submit month by month to various entities. I wrote
the report last year and have been asked to create it again this year.
I know that this is going to be require every year, and I don't want to
write it again.
My problem is that in order to get the month by month data, I had to write
"WHERE (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
tDocHistory.ChangeDate)), CONVERT(CHAR(4),
DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
I would like to be able to allow my users to select the year they wish to
view.
I wanted to replace the 2008 with % in the statement:
(tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
and then filter the datepart for the year by writing:
HAVING (tDocHistory.NewStatus = 'wv' OR
tDocHistory.NewStatus = 'dcs') AND (tCompany.CoName
LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
tDocHistory.ChangeDate))
LIKE @.Year)
Do you know any way I could make this work?
--
SamyraWhy dont you use report parameter to select the date.
On Jan 12, 8:28=A0pm, Samyra <Sam...@.discussions.microsoft.com> wrote:
> I have written a report that contains multiple queries that displays the
> number of applications we submit month by month to various entities. =A0I =wrote
> the report last year and have been asked to create it again this year.
> I know that this is going to be require every year, and I don't want to
> write it again.
> My problem is that in order to get the month by month data, I had to =A0wr=ite
> "WHERE =A0 =A0 (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
> GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
> tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tDocHistory.ChangeDate)), CONV=ERT(CHAR(4),
> DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
> I would like to be able to allow my users to select the year they wish to
> view.
> I wanted to replace the 2008 with % in the statement:
> =A0 (tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
> and then filter the datepart for the year by writing:
> HAVING =A0 =A0 =A0(tDocHistory.NewStatus =3D 'wv' OR
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tDocHistory.NewStatus =3D 'dcs=') AND (tCompany.CoName
> LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
> tDocHistory.ChangeDate))
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 LIKE @.Year)
> Do you know any way I could make this work?
> --
> Samyra|||I would love to, but the report is built with text boxes with a separate
query in each box.
I am looking at a table that contains historical data for each record in the
database, not current data, so I wasn't able to build a table or a matrix
report that would display the data they wanted to see.
--
Samyra
"Sridar K" wrote:
> Why dont you use report parameter to select the date.
> On Jan 12, 8:28 pm, Samyra <Sam...@.discussions.microsoft.com> wrote:
> > I have written a report that contains multiple queries that displays the
> > number of applications we submit month by month to various entities. I wrote
> > the report last year and have been asked to create it again this year.
> >
> > I know that this is going to be require every year, and I don't want to
> > write it again.
> >
> > My problem is that in order to get the month by month data, I had to write
> >
> > "WHERE (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
> > GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
> > tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
> > tDocHistory.ChangeDate)), CONVERT(CHAR(4),
> > DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
> >
> > I would like to be able to allow my users to select the year they wish to
> > view.
> >
> > I wanted to replace the 2008 with % in the statement:
> > (tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
> >
> > and then filter the datepart for the year by writing:
> >
> > HAVING (tDocHistory.NewStatus = 'wv' OR
> > tDocHistory.NewStatus = 'dcs') AND (tCompany.CoName
> > LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
> > tDocHistory.ChangeDate))
> > LIKE @.Year)
> >
> > Do you know any way I could make this work?
> >
> > --
> > Samyra
>

Date format independant of local settings

Hello,
how can we make the Date format for SQLServer7.0 independant of locale settings. We have a Web Server and a database server running multiple projects having different requirement for date formats viz mm/dd/yyyy and dd/mm/yyyy.

I suppose SQLServer takes the database local date format as the current date format. The problem can be solved if i can indicate in my SQL queries the format being used in the query. This also eliminates any accidental change in database server format (from mm/dd/yyyy to dd/mm/yyyy), which would mean 1st Feb being inserted into the database as 2nd Jan.

Any Ideas !!!

Regards,

AshutoshHave you looked at SET DATEFORMAT

SET DATEFORMAT mdy
GO
DECLARE @.datevar datetime
SET @.datevar = '12/31/98'
SELECT @.datevar
GO

SET DATEFORMAT ydm
GO
DECLARE @.datevar datetime
SET @.datevar = '98/31/12'
SELECT @.datevar
GO

SET DATEFORMAT ymd
GO
DECLARE @.datevar datetime
SET @.datevar = '98/12/31'
SELECT @.datevar
GO|||Hello,
thanks the problem seems to be solved by using the SET DATEFORMAT command. While ADODB connection object we have to execute the above command as a action query -
Connection.Execute "SET DATEFORMAT dmy"

The connection thus follows this new date format.

Thanks Again :-)

Regards,

Ashutosh|||This is only a problem with character date formats.
If you always transfer dates with format yyyymmdd then you should never have a problem.

dd mmm yyyy also works as long as you don't use other languages.