Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Add Convert for UTC time Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 8:35 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:32 AM
Points: 215, Visits: 444
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


MCSA SQL Server 2012
Post #1234042
Posted Wednesday, January 11, 2012 8:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1234072
Posted Wednesday, January 11, 2012 9:08 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:32 AM
Points: 215, Visits: 444
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


MCSA SQL Server 2012
Post #1234081
Posted Wednesday, January 11, 2012 9:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1234106
Posted Wednesday, January 11, 2012 9:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 18, 2012 10:58 PM
Points: 63, Visits: 216
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
Post #1234111
Posted Wednesday, January 11, 2012 10:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:32 AM
Points: 215, Visits: 444
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


MCSA SQL Server 2012
Post #1234214
Posted Wednesday, January 11, 2012 11:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 18, 2012 10:58 PM
Points: 63, Visits: 216
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)

Post #1234222
Posted Wednesday, January 11, 2012 11:24 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:32 AM
Points: 215, Visits: 444
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


MCSA SQL Server 2012
Post #1234233
Posted Wednesday, January 11, 2012 11:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 18, 2012 10:58 PM
Points: 63, Visits: 216
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

Post #1234236
Posted Wednesday, January 11, 2012 1:54 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:32 AM
Points: 215, Visits: 444
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


MCSA SQL Server 2012
Post #1234355
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse