Board index » delphi » SQL 2000 - converting date/time to date in aggregate query

SQL 2000 - converting date/time to date in aggregate query

I'm trying to total a sum a currency field (amount) grouped by date.
Unfortunately I used a smalldatatime field to store the date and time
values together.

I couldn't find a function in SQL Server 2000 (help files) that just
gives me the date part that I could use in the group clause.

ie.

select sum(amount) , ConvertToDateOnly???(mydatetime) as mydateonly
from table
group by mydateonly

Is there another way to do this ??

--
Ron Simonsmeier
Enhanced Business Systems Inc.
ro...@enhanced-business.com

 

Re:SQL 2000 - converting date/time to date in aggregate query


After search some previous messages regarding dates, I found someone had
posted the CONVERT(DECIMAL, MyDate) function to convert the date part
only to a BDC field type.

I did discover however, than when I converted the DECIMAL back to a date
in my code, I was short by one day ! I used  "select convert(DECIMAL,
Mydatetime) as MyDateOnlyInteger"  Then, in my Delphi code, I had to
convert the BDC field type back to a date type by MyNewDate :=
MyDateOnlyInteger + 1;

Strange but true and I don't know why this happened.

Quote
Ron Simonsmeier wrote:
> I'm trying to total a sum a currency field (amount) grouped by date.
> Unfortunately I used a smalldatatime field to store the date and time
> values together.

> I couldn't find a function in SQL Server 2000 (help files) that just
> gives me the date part that I could use in the group clause.

> ie.

> select sum(amount) , ConvertToDateOnly???(mydatetime) as mydateonly
> from table
> group by mydateonly

> Is there another way to do this ??

> --
> Ron Simonsmeier
> Enhanced Business Systems Inc.
> ro...@enhanced-business.com

--
Ron Simonsmeier
Enhanced Business Systems Inc.
ro...@enhanced-business.com

Re:SQL 2000 - converting date/time to date in aggregate query


Did you try the DatePart function?

--

Alain Quesnel
cinqsanss...@compuserve.com

Quote
"Ron Simonsmeier" <ro...@enhanced-business.com> wrote in message

news:3CE6EF0A.82F31436@enhanced-business.com...
Quote
> I'm trying to total a sum a currency field (amount) grouped by date.
> Unfortunately I used a smalldatatime field to store the date and time
> values together.

> I couldn't find a function in SQL Server 2000 (help files) that just
> gives me the date part that I could use in the group clause.

> ie.

> select sum(amount) , ConvertToDateOnly???(mydatetime) as mydateonly
> from table
> group by mydateonly

> Is there another way to do this ??

> --
> Ron Simonsmeier
> Enhanced Business Systems Inc.
> ro...@enhanced-business.com

Re:SQL 2000 - converting date/time to date in aggregate query


Why not :

select sum(amount) , convert(datetime, Convert(int, dateTimeFiledHere -
0.5)) as mydateonly
from table
group by mydateonly

Quote
"Ron Simonsmeier" <ro...@enhanced-business.com> wrote in message

news:3CE6EF0A.82F31436@enhanced-business.com...
Quote
> I'm trying to total a sum a currency field (amount) grouped by date.
> Unfortunately I used a smalldatatime field to store the date and time
> values together.

> I couldn't find a function in SQL Server 2000 (help files) that just
> gives me the date part that I could use in the group clause.

> ie.

> select sum(amount) , ConvertToDateOnly???(mydatetime) as mydateonly
> from table
> group by mydateonly

> Is there another way to do this ??

> --
> Ron Simonsmeier
> Enhanced Business Systems Inc.
> ro...@enhanced-business.com

Re:SQL 2000 - converting date/time to date in aggregate query


I suppose I would have to call the function three times to get the complete
date (d/m/y)

i.e. SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)

Quote
Alain Quesnel wrote:
> Did you try the DatePart function?

> --

> Alain Quesnel
> cinqsanss...@compuserve.com
> "Ron Simonsmeier" <ro...@enhanced-business.com> wrote in message
> news:3CE6EF0A.82F31436@enhanced-business.com...
> > I'm trying to total a sum a currency field (amount) grouped by date.
> > Unfortunately I used a smalldatatime field to store the date and time
> > values together.

> > I couldn't find a function in SQL Server 2000 (help files) that just
> > gives me the date part that I could use in the group clause.

> > ie.

> > select sum(amount) , ConvertToDateOnly???(mydatetime) as mydateonly
> > from table
> > group by mydateonly

> > Is there another way to do this ??

> > --
> > Ron Simonsmeier
> > Enhanced Business Systems Inc.
> > ro...@enhanced-business.com

--
Ron Simonsmeier
Enhanced Business Systems Inc.
ro...@enhanced-business.com

Other Threads