SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Add Convert for UTC time


Add Convert for UTC time

Author
Message
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1230 Visits: 919
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26315 Visits: 17553
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.

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)
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1230 Visits: 919
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?

Smile

lk

MCSA SQL Server 2012
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26315 Visits: 17553
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.

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)
stephen99999
stephen99999
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 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
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1230 Visits: 919
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
stephen99999
stephen99999
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 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)


lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1230 Visits: 919
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
stephen99999
stephen99999
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 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


lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1230 Visits: 919
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 :-D

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search