Is there a simpler way to group an SQL query by minute?
SELECT
datepart(year, DateTimeColumn)
datepart(month, DateTimeColumn)
datepart(day, DateTimeColumn)
datepart(hour, DateTimeColumn)
datepart(minute, DateTimeColumn)
FROM SomeTable GROUP BY
datepart(year, DateTimeColumn)
datepart(month, DateTimeColumn)
datepart(day, DateTimeColumn)
datepart(hour, DateTimeColumn)
datepart(minute, DateTimeColumn)
How could I get the result back as a datetime rather than 5 separate date related columns?SELECT cast(convert(varchar(16), getdate(), 120) as datetime)
FROM YourTable
GROUP BY convert(varchar(16), DateTimeColumn, 120)
blindman|||This takes a minute and half to run, depending on the box
What about..
USE Northwind
GO
SET NOCOUNT ON
DECLARE @.myTable99 TABLE (Col1 int IDENTITY(1,1), Col2 datetime DEFAULT GetDate(), Col3 char(1))
DECLARE @.i int
SELECT @.i = 0
WHILE @.i < 1000000
BEGIN
INSERT INTO @.myTable99 (Col3) SELECT 'x'
SELECT @.i = @.i + 1
END
SET NOCOUNT OFF
SELECT CONVERT(varchar(26),Col2,101), DATEPART(n,Col2), COUNT(*)
FROM @.myTable99
GROUP BY CONVERT(varchar(26),Col2,101), DATEPART(n,Col2)|||Originally posted by Brett Kaiser
This takes a minute and half to run, depending on the box
What about..
That won't work. It will separate between different dates but not different hours; 3:31 and 4:31 will be grouped together. Try the following which does a few manual INSERTS.
USE Northwind
GO
SET NOCOUNT ON
DECLARE @.myTable99 TABLE (Col1 int IDENTITY(1,1), Col2 datetime DEFAULT GetDate(), Col3 char(1))
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 0, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 0, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.myTable99 (Col2, Col3) VALUES (dateadd(minute, 61, getdate()), 'x')
SET NOCOUNT OFF
SELECT CONVERT(varchar(26),Col2,101), DATEPART(n,Col2), COUNT(*)
FROM @.myTable99
GROUP BY CONVERT(varchar(26),Col2,101), DATEPART(n,Col2)|||Originally posted by blindman
SELECT cast(convert(varchar(16), getdate(), 120) as datetime)
FROM YourTable
GROUP BY convert(varchar(16), DateTimeColumn, 120)
blindman
I had to add the cast in the group by section as well but then it works perfectly! Thanks!
USE Northwind
GO
DECLARE @.sampleTable TABLE (IdColumn int IDENTITY(1,1), DateColumn datetime DEFAULT GetDate(), DataColumn char(1))
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 0, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 0, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 1, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')
INSERT INTO @.sampleTable (DateColumn, DataColumn) VALUES (dateadd(minute, 61, getdate()), 'x')
SELECT COUNT(*), CAST(CONVERT(varchar(16), DateColumn, 120) AS datetime)
FROM @.sampleTable
GROUP BY CAST(CONVERT(varchar(16), DateColumn, 120) AS datetime)|||Good point...how about
USE Northwind
GO
SET NOCOUNT ON
DECLARE @.myTable99 TABLE (Col1 int IDENTITY(1,1), Col2 datetime DEFAULT GetDate(), Col3 char(1))
DECLARE @.i int
SELECT @.i = 0
WHILE @.i < 1000000
BEGIN
INSERT INTO @.myTable99 (Col3) SELECT 'x'
SELECT @.i = @.i + 1
END
SET NOCOUNT OFF
SELECT SUBSTRING(CONVERT(varchar(26),Col2,120),1,13), DATEPART(n,Col2), COUNT(*)
FROM @.myTable99
GROUP BY SUBSTRING(CONVERT(varchar(26),Col2,120),1,13), DATEPART(n,Col2)
Returns:
---- ---- ----
2003-11-11 15 2 365972
2003-11-11 15 3 634028
(2 row(s) affected)
Showing posts with label datepart. Show all posts
Showing posts with label datepart. Show all posts
Wednesday, March 7, 2012
Sunday, February 26, 2012
GROUP BY DATEPART issues...
Hi,
I'm trying:
Code Snippet
SELECT SUM(price), DATEPART(month, order_date), DATEPART(year, order_date)
FROM orders
GROUP BY DATEPART(month, order_date), DATEPART(year, order_date)
and, while this executes w/o a problem, I am a bit concerned with my results...
i get a SUM(price) = 2140.21 when running the above SQL for 11/2006
however, when i try:
Code Snippet
SELECT SUM(price)
FROM orders
WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'
i get a SUM(price) = 1950.45
if i bump the second date up by one day (i.e. '12/01/2006') i get a SUM(price) = 2140.21, the same value as when I used GROUP BY
any help would be greatly appreciated!
Hi,
What is the result of this query
Code Snippet
SELECT SUM(price), month(order_date), year(order_date)
FROM orders
GROUP BY month(order_date), year(order_date)
HAVING month(order_date)=6 AND year(order_date)=2006
If result is 1950.45 then
u try to use like this syntax
else
check your data one by one ..
|||Thank you for the response.
I have tried
Code Snippet
...
HAVING DATEPART(month, order_date) = 11 AND DATEPART(year, order_date) = 2006
and received 2140.21
|||Is the order_date always truncated at midnight? Try replacing "SUM(price)" with "COUNT(*)" with your queries. Do you get the same record counts for November 2006?
Thanks,
Bryan Smith
I'm not sure about order_date being truncated at midnight... order_date is a datetime, so i am assuming that up until 11:59:59.99PM on 11/30, the date parts I am after remain the same.
I ran the COUNT(*) as suggested and get 804 using:
Code Snippet
SELECT COUNT(*)
FROM orders
WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'
and get 838 when using:
Code Snippet
SELECT COUNT(*), DATEPART(month, order_date), DATEPART(year, order_date)
FROM orders
GROUP BY DATEPART(month, order_date), DATEPART(year, order_date)
the number remains 838 when I add:
Code Snippet
HAVING DATEPART(month, order_date) = 11 AND DATEPART(year, order_date) = 2006and drops to 804 if i add:
Code Snippet
WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'i am baffled, but then again, i'm no expert
thanks for the assistance!
|||Cool! I think we're on the right track here.
Your original code used "WHERE order_date BETWEEN '11/01/2006' and '11/30/2006'". That means orders created between midnight Nov 1 2006 and midnight at the top of Nov 30 2006. You're dropping orders that occurred from 11/30/2006 12:00:00.001 AM to 11/30/2006 11:59:59.997 PM. That's why the BETWEEN statement gives you 804 records while the DATEPART statement gives you 838 records.
(Please note, SQL Server is only accurate to 3 ms when a datetime data type is used. Times of 11:59:59.998 PM and 11:59:59.999 PM are recorded as 12:00:00.000 AM the next day.)
If you re-write your query to use "WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006 11:59:59.997 PM'" you should get 838 records and your SUM should match the one in the DATEPART query.
Good luck,
Bryan
Ahhh... that makes sense...
would it be correct to assume that the GROUP BY DATEPART... query accurately sums up each months data?
thanks again for your help!
|||It would. It calculates the month of the date without regard for time, so any orders placed at anytime on 11/30/2006 would fall into November.
B.
Group by Datepart 30 sec interval?
Hello, im starting with sql and i need to group data in the form of:
datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
datepart(30*s,date). Is any way of getting it?
I know i can get the same in vb passing the interval by parameter, by is it
possible to do it this way?
Thanks a lot!!!
Sorry, for my English
This example takes a datetime and truncates it to 30 second intervals.
By grouping on the expression you should get what you need.
select crdate,
dateadd(ms,- (datepart(ms,crdate) +
((datepart(second,crdate) % 30) * 1000)),
crdate)
from sysobjects
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Hello, im starting with sql and i need to group data in the form of:
> datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>datepart(30*s,date). Is any way of getting it?
>I know i can get the same in vb passing the interval by parameter, by is it
>possible to do it this way?
>Thanks a lot!!!
>Sorry, for my English
|||Thanks for answering!! As I told before I��m starting so I��m not very good at
this. Would you mind explainig a bit how it works? Cause I do not see it. And
i supposse the ms stands for miliseconds, but vba in excel gives me object
error.
Thanks a lot indeed!!
"Roy Harvey" wrote:
> This example takes a datetime and truncates it to 30 second intervals.
> By grouping on the expression you should get what you need.
> select crdate,
> dateadd(ms,- (datepart(ms,crdate) +
> ((datepart(second,crdate) % 30) * 1000)),
> crdate)
> from sysobjects
> Roy Harvey
> Beacon Falls, CT
> On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> wrote:
>
|||Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
work. Only problem, is that it does not accept ms in vba.
Thanks a lot!!!!
"neb" wrote:
[vbcol=seagreen]
> Thanks for answering!! As I told before I��m starting so I��m not very good at
> this. Would you mind explainig a bit how it works? Cause I do not see it. And
> i supposse the ms stands for miliseconds, but vba in excel gives me object
> error.
> Thanks a lot indeed!!
> "Roy Harvey" wrote:
|||What I wrote is Transact-SQL, the language understood by Microsoft SQL
Server. It is not VBA. I suggest getting your queries working in
Query Analyzer (SQL Server 2000) or SQL Management Studio (SQL Server
2005) before trying to run them through any programming tool.
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 11:47:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
[vbcol=seagreen]
>Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
>work. Only problem, is that it does not accept ms in vba.
>Thanks a lot!!!!
>"neb" wrote:
datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
datepart(30*s,date). Is any way of getting it?
I know i can get the same in vb passing the interval by parameter, by is it
possible to do it this way?
Thanks a lot!!!
Sorry, for my English
This example takes a datetime and truncates it to 30 second intervals.
By grouping on the expression you should get what you need.
select crdate,
dateadd(ms,- (datepart(ms,crdate) +
((datepart(second,crdate) % 30) * 1000)),
crdate)
from sysobjects
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Hello, im starting with sql and i need to group data in the form of:
> datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>datepart(30*s,date). Is any way of getting it?
>I know i can get the same in vb passing the interval by parameter, by is it
>possible to do it this way?
>Thanks a lot!!!
>Sorry, for my English
|||Thanks for answering!! As I told before I��m starting so I��m not very good at
this. Would you mind explainig a bit how it works? Cause I do not see it. And
i supposse the ms stands for miliseconds, but vba in excel gives me object
error.
Thanks a lot indeed!!
"Roy Harvey" wrote:
> This example takes a datetime and truncates it to 30 second intervals.
> By grouping on the expression you should get what you need.
> select crdate,
> dateadd(ms,- (datepart(ms,crdate) +
> ((datepart(second,crdate) % 30) * 1000)),
> crdate)
> from sysobjects
> Roy Harvey
> Beacon Falls, CT
> On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> wrote:
>
|||Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
work. Only problem, is that it does not accept ms in vba.
Thanks a lot!!!!
"neb" wrote:
[vbcol=seagreen]
> Thanks for answering!! As I told before I��m starting so I��m not very good at
> this. Would you mind explainig a bit how it works? Cause I do not see it. And
> i supposse the ms stands for miliseconds, but vba in excel gives me object
> error.
> Thanks a lot indeed!!
> "Roy Harvey" wrote:
|||What I wrote is Transact-SQL, the language understood by Microsoft SQL
Server. It is not VBA. I suggest getting your queries working in
Query Analyzer (SQL Server 2000) or SQL Management Studio (SQL Server
2005) before trying to run them through any programming tool.
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 11:47:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
[vbcol=seagreen]
>Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
>work. Only problem, is that it does not accept ms in vba.
>Thanks a lot!!!!
>"neb" wrote:
Group by Datepart 30 sec interval?
Hello, im starting with sql and i need to group data in the form of:
datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
datepart(30*s,date). Is any way of getting it?
I know i can get the same in vb passing the interval by parameter, by is it
possible to do it this way?
Thanks a lot!!!
Sorry, for my EnglishThis example takes a datetime and truncates it to 30 second intervals.
By grouping on the expression you should get what you need.
select crdate,
dateadd(ms,- (datepart(ms,crdate) +
((datepart(second,crdate) % 30) * 1000)),
crdate)
from sysobjects
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Hello, im starting with sql and i need to group data in the form of:
> datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>datepart(30*s,date). Is any way of getting it?
>I know i can get the same in vb passing the interval by parameter, by is it
>possible to do it this way?
>Thanks a lot!!!
>Sorry, for my English|||Thanks for answering!! As I told before I��m starting so I��m not very good
at
this. Would you mind explainig a bit how it works? Cause I do not see it. An
d
i supposse the ms stands for miliseconds, but vba in excel gives me object
error.
Thanks a lot indeed!!
"Roy Harvey" wrote:
> This example takes a datetime and truncates it to 30 second intervals.
> By grouping on the expression you should get what you need.
> select crdate,
> dateadd(ms,- (datepart(ms,crdate) +
> ((datepart(second,crdate) % 30) * 1000)),
> crdate)
> from sysobjects
> Roy Harvey
> Beacon Falls, CT
> On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> wrote:
>
>|||Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
work. Only problem, is that it does not accept ms in vba.
Thanks a lot!!!!
"neb" wrote:
[vbcol=seagreen]
> Thanks for answering!! As I told before I��m starting so I��m not very goo
d at
> this. Would you mind explainig a bit how it works? Cause I do not see it.
And
> i supposse the ms stands for miliseconds, but vba in excel gives me object
> error.
> Thanks a lot indeed!!
> "Roy Harvey" wrote:
>|||What I wrote is Transact-SQL, the language understood by Microsoft SQL
Server. It is not VBA. I suggest getting your queries working in
Query Analyzer (SQL Server 2000) or SQL Management Studio (SQL Server
2005) before trying to run them through any programming tool.
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 11:47:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
[vbcol=seagreen]
>Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it shoul
d
>work. Only problem, is that it does not accept ms in vba.
>Thanks a lot!!!!
>"neb" wrote:
>
datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
datepart(30*s,date). Is any way of getting it?
I know i can get the same in vb passing the interval by parameter, by is it
possible to do it this way?
Thanks a lot!!!
Sorry, for my EnglishThis example takes a datetime and truncates it to 30 second intervals.
By grouping on the expression you should get what you need.
select crdate,
dateadd(ms,- (datepart(ms,crdate) +
((datepart(second,crdate) % 30) * 1000)),
crdate)
from sysobjects
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Hello, im starting with sql and i need to group data in the form of:
> datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>datepart(30*s,date). Is any way of getting it?
>I know i can get the same in vb passing the interval by parameter, by is it
>possible to do it this way?
>Thanks a lot!!!
>Sorry, for my English|||Thanks for answering!! As I told before I��m starting so I��m not very good
at
this. Would you mind explainig a bit how it works? Cause I do not see it. An
d
i supposse the ms stands for miliseconds, but vba in excel gives me object
error.
Thanks a lot indeed!!
"Roy Harvey" wrote:
> This example takes a datetime and truncates it to 30 second intervals.
> By grouping on the expression you should get what you need.
> select crdate,
> dateadd(ms,- (datepart(ms,crdate) +
> ((datepart(second,crdate) % 30) * 1000)),
> crdate)
> from sysobjects
> Roy Harvey
> Beacon Falls, CT
> On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> wrote:
>
>|||Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
work. Only problem, is that it does not accept ms in vba.
Thanks a lot!!!!
"neb" wrote:
[vbcol=seagreen]
> Thanks for answering!! As I told before I��m starting so I��m not very goo
d at
> this. Would you mind explainig a bit how it works? Cause I do not see it.
And
> i supposse the ms stands for miliseconds, but vba in excel gives me object
> error.
> Thanks a lot indeed!!
> "Roy Harvey" wrote:
>|||What I wrote is Transact-SQL, the language understood by Microsoft SQL
Server. It is not VBA. I suggest getting your queries working in
Query Analyzer (SQL Server 2000) or SQL Management Studio (SQL Server
2005) before trying to run them through any programming tool.
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 11:47:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
[vbcol=seagreen]
>Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it shoul
d
>work. Only problem, is that it does not accept ms in vba.
>Thanks a lot!!!!
>"neb" wrote:
>
Group by Datepart 30 sec interval?
Hello, im starting with sql and i need to group data in the form of:
datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
datepart(30*s,date). Is any way of getting it?
I know i can get the same in vb passing the interval by parameter, by is it
possible to do it this way?
Thanks a lot!!!
Sorry, for my EnglishThis example takes a datetime and truncates it to 30 second intervals.
By grouping on the expression you should get what you need.
select crdate,
dateadd(ms,- (datepart(ms,crdate) +
((datepart(second,crdate) % 30) * 1000)),
crdate)
from sysobjects
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Hello, im starting with sql and i need to group data in the form of:
> datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>datepart(30*s,date). Is any way of getting it?
>I know i can get the same in vb passing the interval by parameter, by is it
>possible to do it this way?
>Thanks a lot!!!
>Sorry, for my English|||Thanks for answering!! As I told before I´m starting so I´m not very good at
this. Would you mind explainig a bit how it works? Cause I do not see it. And
i supposse the ms stands for miliseconds, but vba in excel gives me object
error.
Thanks a lot indeed!!
"Roy Harvey" wrote:
> This example takes a datetime and truncates it to 30 second intervals.
> By grouping on the expression you should get what you need.
> select crdate,
> dateadd(ms,- (datepart(ms,crdate) +
> ((datepart(second,crdate) % 30) * 1000)),
> crdate)
> from sysobjects
> Roy Harvey
> Beacon Falls, CT
> On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> wrote:
> >Hello, im starting with sql and i need to group data in the form of:
> > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
> >datepart(30*s,date). Is any way of getting it?
> >
> >I know i can get the same in vb passing the interval by parameter, by is it
> >possible to do it this way?
> >
> >Thanks a lot!!!
> >
> >Sorry, for my English
>|||Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
work. Only problem, is that it does not accept ms in vba.
Thanks a lot!!!!
"neb" wrote:
> Thanks for answering!! As I told before I´m starting so I´m not very good at
> this. Would you mind explainig a bit how it works? Cause I do not see it. And
> i supposse the ms stands for miliseconds, but vba in excel gives me object
> error.
> Thanks a lot indeed!!
> "Roy Harvey" wrote:
> > This example takes a datetime and truncates it to 30 second intervals.
> > By grouping on the expression you should get what you need.
> >
> > select crdate,
> > dateadd(ms,- (datepart(ms,crdate) +
> > ((datepart(second,crdate) % 30) * 1000)),
> > crdate)
> > from sysobjects
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> > wrote:
> >
> > >Hello, im starting with sql and i need to group data in the form of:
> > > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
> > >datepart(30*s,date). Is any way of getting it?
> > >
> > >I know i can get the same in vb passing the interval by parameter, by is it
> > >possible to do it this way?
> > >
> > >Thanks a lot!!!
> > >
> > >Sorry, for my English
> >|||What I wrote is Transact-SQL, the language understood by Microsoft SQL
Server. It is not VBA. I suggest getting your queries working in
Query Analyzer (SQL Server 2000) or SQL Management Studio (SQL Server
2005) before trying to run them through any programming tool.
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 11:47:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
>work. Only problem, is that it does not accept ms in vba.
>Thanks a lot!!!!
>"neb" wrote:
>> Thanks for answering!! As I told before I´m starting so I´m not very good at
>> this. Would you mind explainig a bit how it works? Cause I do not see it. And
>> i supposse the ms stands for miliseconds, but vba in excel gives me object
>> error.
>> Thanks a lot indeed!!
>> "Roy Harvey" wrote:
>> > This example takes a datetime and truncates it to 30 second intervals.
>> > By grouping on the expression you should get what you need.
>> >
>> > select crdate,
>> > dateadd(ms,- (datepart(ms,crdate) +
>> > ((datepart(second,crdate) % 30) * 1000)),
>> > crdate)
>> > from sysobjects
>> >
>> > Roy Harvey
>> > Beacon Falls, CT
>> >
>> > On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
>> > wrote:
>> >
>> > >Hello, im starting with sql and i need to group data in the form of:
>> > > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>> > >datepart(30*s,date). Is any way of getting it?
>> > >
>> > >I know i can get the same in vb passing the interval by parameter, by is it
>> > >possible to do it this way?
>> > >
>> > >Thanks a lot!!!
>> > >
>> > >Sorry, for my English
>> >
datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
datepart(30*s,date). Is any way of getting it?
I know i can get the same in vb passing the interval by parameter, by is it
possible to do it this way?
Thanks a lot!!!
Sorry, for my EnglishThis example takes a datetime and truncates it to 30 second intervals.
By grouping on the expression you should get what you need.
select crdate,
dateadd(ms,- (datepart(ms,crdate) +
((datepart(second,crdate) % 30) * 1000)),
crdate)
from sysobjects
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Hello, im starting with sql and i need to group data in the form of:
> datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>datepart(30*s,date). Is any way of getting it?
>I know i can get the same in vb passing the interval by parameter, by is it
>possible to do it this way?
>Thanks a lot!!!
>Sorry, for my English|||Thanks for answering!! As I told before I´m starting so I´m not very good at
this. Would you mind explainig a bit how it works? Cause I do not see it. And
i supposse the ms stands for miliseconds, but vba in excel gives me object
error.
Thanks a lot indeed!!
"Roy Harvey" wrote:
> This example takes a datetime and truncates it to 30 second intervals.
> By grouping on the expression you should get what you need.
> select crdate,
> dateadd(ms,- (datepart(ms,crdate) +
> ((datepart(second,crdate) % 30) * 1000)),
> crdate)
> from sysobjects
> Roy Harvey
> Beacon Falls, CT
> On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> wrote:
> >Hello, im starting with sql and i need to group data in the form of:
> > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
> >datepart(30*s,date). Is any way of getting it?
> >
> >I know i can get the same in vb passing the interval by parameter, by is it
> >possible to do it this way?
> >
> >Thanks a lot!!!
> >
> >Sorry, for my English
>|||Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
work. Only problem, is that it does not accept ms in vba.
Thanks a lot!!!!
"neb" wrote:
> Thanks for answering!! As I told before I´m starting so I´m not very good at
> this. Would you mind explainig a bit how it works? Cause I do not see it. And
> i supposse the ms stands for miliseconds, but vba in excel gives me object
> error.
> Thanks a lot indeed!!
> "Roy Harvey" wrote:
> > This example takes a datetime and truncates it to 30 second intervals.
> > By grouping on the expression you should get what you need.
> >
> > select crdate,
> > dateadd(ms,- (datepart(ms,crdate) +
> > ((datepart(second,crdate) % 30) * 1000)),
> > crdate)
> > from sysobjects
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> > wrote:
> >
> > >Hello, im starting with sql and i need to group data in the form of:
> > > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
> > >datepart(30*s,date). Is any way of getting it?
> > >
> > >I know i can get the same in vb passing the interval by parameter, by is it
> > >possible to do it this way?
> > >
> > >Thanks a lot!!!
> > >
> > >Sorry, for my English
> >|||What I wrote is Transact-SQL, the language understood by Microsoft SQL
Server. It is not VBA. I suggest getting your queries working in
Query Analyzer (SQL Server 2000) or SQL Management Studio (SQL Server
2005) before trying to run them through any programming tool.
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 11:47:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
>work. Only problem, is that it does not accept ms in vba.
>Thanks a lot!!!!
>"neb" wrote:
>> Thanks for answering!! As I told before I´m starting so I´m not very good at
>> this. Would you mind explainig a bit how it works? Cause I do not see it. And
>> i supposse the ms stands for miliseconds, but vba in excel gives me object
>> error.
>> Thanks a lot indeed!!
>> "Roy Harvey" wrote:
>> > This example takes a datetime and truncates it to 30 second intervals.
>> > By grouping on the expression you should get what you need.
>> >
>> > select crdate,
>> > dateadd(ms,- (datepart(ms,crdate) +
>> > ((datepart(second,crdate) % 30) * 1000)),
>> > crdate)
>> > from sysobjects
>> >
>> > Roy Harvey
>> > Beacon Falls, CT
>> >
>> > On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
>> > wrote:
>> >
>> > >Hello, im starting with sql and i need to group data in the form of:
>> > > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>> > >datepart(30*s,date). Is any way of getting it?
>> > >
>> > >I know i can get the same in vb passing the interval by parameter, by is it
>> > >possible to do it this way?
>> > >
>> > >Thanks a lot!!!
>> > >
>> > >Sorry, for my English
>> >
Subscribe to:
Posts (Atom)