January 11, 2012 at 8:35 am
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 statement
SELECT ServerName, COUNT(ServerName) AS [Count], Value, Heading, UTCOccurrenceDateTime
FROM Alerts
WHERE (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, UTCOccurrenceDateTime
HAVING (COUNT(ServerName) < 2500000)
ORDER BY UTCOccurrenceDateTime, ServerName
MCSE SQL Server 2012\2014\2016
January 11, 2012 at 8:59 am
Are you just trying to format the UTCOccurrenceDateTime column?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 11, 2012 at 9:08 am
Yes, 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?
🙂
lk
MCSE SQL Server 2012\2014\2016
January 11, 2012 at 9:22 am
Well 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). http://msdn.microsoft.com/en-us/library/ms187928.aspx
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?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 11, 2012 at 9:25 am
worker bee (1/11/2012)
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 statement
SELECT ServerName, COUNT(ServerName) AS [Count], Value, Heading, UTCOccurrenceDateTime
FROM Alerts
WHERE (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, UTCOccurrenceDateTime
HAVING (COUNT(ServerName) < 2500000)
ORDER BY UTCOccurrenceDateTime, ServerName
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 Alerts
WHERE (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, UTCOccurrenceDateTime
HAVING (COUNT(ServerName) < 2500000)
for helpful date conversions: Try this
also see: datetime
Stephen
January 11, 2012 at 10:57 am
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
MCSE SQL Server 2012\2014\2016
January 11, 2012 at 11:08 am
worker bee (1/11/2012)
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
Did you try the converts?
SELECT CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111)
--or
SELECT CONVERT(DATE, UTCOccurrenceDateTime, 111)
January 11, 2012 at 11:24 am
This converted the date but now I need to count...
SELECT
CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111)
FROM Alerts
WHERE (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, UTCOccurrenceDateTime
HAVING (COUNT(Heading) < 2500000)
ORDER BY UTCOccurrenceDateTime, ServerName
MCSE SQL Server 2012\2014\2016
January 11, 2012 at 11:30 am
worker bee (1/11/2012)
This converted the date but now I need to count...SELECT
CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111)
FROM Alerts
WHERE (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, UTCOccurrenceDateTime
HAVING (COUNT(Heading) < 2500000)
ORDER BY UTCOccurrenceDateTime, ServerName
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.
SELECT
count(CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111)) as 'CountDate'
FROM Alerts
WHERE (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, UTCOccurrenceDateTime
HAVING (COUNT(Heading) < 2500000)
ORDER BY UTCOccurrenceDateTime, ServerName
January 11, 2012 at 1:54 pm
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 😀
SELECT COUNT(*) AS [Count], Heading, Value, ServerName, Metric,
CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111) AS 'CountDate'
FROM dbo.Alerts
WHERE (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
MCSE SQL Server 2012\2014\2016
January 11, 2012 at 1:59 pm
worker bee (1/11/2012)
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 😀SELECT COUNT(*) AS [Count], Heading, Value, ServerName, Metric,
CONVERT(VARCHAR(10), UTCOccurrenceDateTime, 111) AS 'CountDate'
FROM dbo.Alerts
WHERE (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
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.
-Stephen
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply