InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 19:52:01 GMT20http://www.sqlservercentral.com/Forums/Topic1234042-1292-1.aspx[quote][b]worker bee (1/11/2012)[/b][hr]That got it, I had to do some working around on it but I got what I was looking for. Thank you for putting me in the right direction. Next time I will try to more descriptive, sorry about that. I am just a complicated nerd...lol :-DSELECT COUNT(*) AS [Count], Heading, Value, ServerName, Metric,CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111) AS 'CountDate' FROM dbo.AlertsWHERE (Heading LIKE 'Disk %') AND (Heading NOT LIKE 'Disk C%') AND (Value >= 95.00) AND (Metric = 64)AND (UTCOccurrenceDateTime >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0))GROUP BY Heading, Value, ServerName, Metric,CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111)HAVING(COUNT(Heading) < 2500000) ORDER BY ServerName[/quote]I hear ya. Especially when something small really stumps you. Also, check the link I added earlier about date conversions. Seems like a useful site to bookmark. I am always forgetting datetime conversions/casts lol.-StephenWed, 11 Jan 2012 13:59:39 GMTstephen99999http://www.sqlservercentral.com/Forums/Topic1234042-1292-1.aspxThat got it, I had to do some working around on it but I got what I was looking for. Thank you for putting me in the right direction. Next time I will try to more descriptive, sorry about that. I am just a complicated nerd...lol :-DSELECT COUNT(*) AS [Count], Heading, Value, ServerName, Metric,CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111) AS 'CountDate' FROM dbo.AlertsWHERE (Heading LIKE 'Disk %') AND (Heading NOT LIKE 'Disk C%') AND (Value >= 95.00) AND (Metric = 64)AND (UTCOccurrenceDateTime >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0))GROUP BY Heading, Value, ServerName, Metric,CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111)HAVING(COUNT(Heading) < 2500000) ORDER BY ServerNameWed, 11 Jan 2012 13:54:30 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1234042-1292-1.aspx[quote][b]worker bee (1/11/2012)[/b][hr]This converted the date but now I need to count...SELECTCONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111)FROM AlertsWHERE (Heading LIKE 'Disk %') AND (Heading NOT LIKE 'Disk C%') AND (Value >= 95.00) AND (UTCOccurrenceDateTime >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0))GROUP BY Heading, Value, ServerName, UTCOccurrenceDateTimeHAVING (COUNT(Heading) < 2500000)ORDER BY UTCOccurrenceDateTime, ServerName[/quote]easy enough. Are you receiving errors or something? if so it would be wonderful of you to post them. Also, in the future, you should really try to be more descriptive in your problem, question, and desired result.[code="sql"]SELECTcount(CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111)) as 'CountDate'FROM AlertsWHERE (Heading LIKE 'Disk %') AND (Heading NOT LIKE 'Disk C%') AND (Value >= 95.00) AND (UTCOccurrenceDateTime >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0))GROUP BY Heading, Value, ServerName, UTCOccurrenceDateTimeHAVING (COUNT(Heading) < 2500000)ORDER BY UTCOccurrenceDateTime, ServerName[/code]Wed, 11 Jan 2012 11:30:12 GMTstephen99999http://www.sqlservercentral.com/Forums/Topic1234042-1292-1.aspxThis converted the date but now I need to count...SELECTCONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111)FROM AlertsWHERE (Heading LIKE 'Disk %') AND (Heading NOT LIKE 'Disk C%') AND (Value >= 95.00) AND (UTCOccurrenceDateTime >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0))GROUP BY Heading, Value, ServerName, UTCOccurrenceDateTimeHAVING (COUNT(Heading) < 2500000)ORDER BY UTCOccurrenceDateTime, ServerNameWed, 11 Jan 2012 11:24:35 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1234042-1292-1.aspx[quote][b]worker bee (1/11/2012)[/b][hr]I get an error on the CAST;select ServerName, Heading, UTCOccurrenceDateTime REPLACE(CAST(UTCOccurrenceDateTime as date),'-','/')from dbo.Alerts where Heading like '%blocking%'AND DATEADD(hh, -6, UTCOccurrenceDateTime) >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0)order by UTCOccurrenceDateTime[/quote]Did you try the converts?[code="sql"]SELECT CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111)--orSELECT CONVERT(DATE, UTCOccurrenceDateTime, 111)[/code]Wed, 11 Jan 2012 11:08:36 GMTstephen99999http://www.sqlservercentral.com/Forums/Topic1234042-1292-1.aspxI get an error on the CAST;select ServerName, Heading, UTCOccurrenceDateTime REPLACE(CAST(UTCOccurrenceDateTime as date),'-','/')from dbo.Alerts where Heading like '%blocking%'AND DATEADD(hh, -6, UTCOccurrenceDateTime) >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0)order by UTCOccurrenceDateTimeWed, 11 Jan 2012 10:57:19 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1234042-1292-1.aspx[quote][b]worker bee (1/11/2012)[/b][hr]Okay, I have to add a convert for the UTC column and I have no idea where to put it, can some one help me out real quick?add convert UTCdatetime to yyyy/mm/dd in sql statementSELECT ServerName, COUNT(ServerName) AS [Count], Value, Heading, UTCOccurrenceDateTimeFROM AlertsWHERE (Heading LIKE 'Disk %') AND (Heading NOT LIKE 'Disk C%') AND (Value >= 95.00) AND DATEADD(hh, -6, UTCOccurrenceDateTime) >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0)GROUP BY Heading, Value, ServerName, UTCOccurrenceDateTimeHAVING (COUNT(ServerName) < 2500000)ORDER BY UTCOccurrenceDateTime, ServerName[/quote][code="sql"]SELECT ServerName, COUNT(ServerName) AS [Count], Value, Heading, REPLACE(CAST(UTCOccurrenceDateTime as date),'-','/') --or try SELECT CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111) --or try SELECT CONVERT(DATE, UTCOccurrenceDateTime, 111)FROM AlertsWHERE (Heading LIKE 'Disk %') AND (Heading NOT LIKE 'Disk C%') AND (Value >= 95.00) AND DATEADD(hh, -6, UTCOccurrenceDateTime) >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0)GROUP BY Heading, Value, ServerName, UTCOccurrenceDateTimeHAVING (COUNT(ServerName) < 2500000)[/code]for helpful date conversions: [url=http://www.sql-server-helper.com/tips/date-formats.aspx]Try this[/url]also see: [url=http://msdn.microsoft.com/en-us/library/ms187928.aspx]datetime[/url]StephenWed, 11 Jan 2012 09:25:21 GMTstephen99999http://www.sqlservercentral.com/Forums/Topic1234042-1292-1.aspxWell you say you want to convert but then you want to make it central time. I am a little confused.Convert is used for presentation (which really belongs in the front end). [url=http://msdn.microsoft.com/en-us/library/ms187928.aspx]http://msdn.microsoft.com/en-us/library/ms187928.aspx[/url]If you are wanting to use UTC time and change that to central time you will need to do some date math DATEADD, DATEDIFF etc. This can be a real problem if you want accurate data due to daylight saving changes. Does that help?Wed, 11 Jan 2012 09:22:40 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1234042-1292-1.aspxYes, I am trying to conver the UTC time to Central time and count servername and UTCOccurrances so I can take the query from 50000 rows to once sername per date that a job failed. Make Sense?:)lkWed, 11 Jan 2012 09:08:22 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1234042-1292-1.aspxAre you just trying to format the UTCOccurrenceDateTime column?Wed, 11 Jan 2012 08:59:57 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1234042-1292-1.aspxOkay, I have to add a convert for the UTC column and I have no idea where to put it, can some one help me out real quick?add convert UTCdatetime to yyyy/mm/dd in sql statementSELECT ServerName, COUNT(ServerName) AS [Count], Value, Heading, UTCOccurrenceDateTimeFROM AlertsWHERE (Heading LIKE 'Disk %') AND (Heading NOT LIKE 'Disk C%') AND (Value >= 95.00) AND DATEADD(hh, -6, UTCOccurrenceDateTime) >= DATEADD(day, DATEDIFF(day, 0, GETUTCDATE()), 0)GROUP BY Heading, Value, ServerName, UTCOccurrenceDateTimeHAVING (COUNT(ServerName) < 2500000)ORDER BY UTCOccurrenceDateTime, ServerNameWed, 11 Jan 2012 08:35:38 GMTIt's the Database!!!