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:

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:
>

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
>> >

GROUP BY and aggregate functions not supported with FOR XML AUTO

Hi All
I am trying to ouput the results from my query in the form of XML. The query
is like this:
SELECT a, b, COUNT(S.c ) AS x
FROM s
GROUP BY a,b
ORDER BY a,b
FOR XML AUTO, ELEMENTS
If run this, i get an error like this:
Server: Msg 6821, Level 16, State 1, Line 1
GROUP BY and aggregate functions are currently not supported with FOR XML
AUTO.
Is there any way i can do this? Thank you all in advance.MittyKom,
Try:
SELECT * FROM (SELECT TOP 100 PERCENT a, b, COUNT(c) AS x
FROM s
GROUP BY a,b
ORDER BY a,b ) AS Y
FOR XML AUTO, ELEMENTS
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:2D6F411D-7CA3-4EEB-A05F-1FBF03FFE7E3@.microsoft.com...
> Hi All
> I am trying to ouput the results from my query in the form of XML. The
> query
> is like this:
> SELECT a, b, COUNT(S.c ) AS x
> FROM s
> GROUP BY a,b
> ORDER BY a,b
> FOR XML AUTO, ELEMENTS
>
> If run this, i get an error like this:
> Server: Msg 6821, Level 16, State 1, Line 1
> GROUP BY and aggregate functions are currently not supported with FOR XML
> AUTO.
> Is there any way i can do this? Thank you all in advance.|||SELECT a, b, x
FROM
(SELECT a, b, COUNT(S.c) AS x
FROM S
GROUP BY a, b) AS T
ORDER BY a, b
FOR XML AUTO, ELEMENTS ;
David Portas
SQL Server MVP
--

Grid view-cant update or delete

I put a grid view on a web form ,when I run it -the SELECT, EDIT works

the UPDATE,DELETE makes an error although I use the sama data,I added the error :

Anyone can help?

Server Error in '/CrystalReportsWebSite1' Application.

---

The data types text and nvarchar are incompatible in the equal to operator.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): The data types text and nvarchar are incompatible in the equal to operator.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140

Hey,

What does those update/delete stored procedures look like? It seems like it may be an issue with the query.

|||I'm guessing he has a text field, and he told it to use optimistic concurrency or (CompareAllValues), which doesn't work with text fields.