March 4, 2016 at 5:22 am
Hi,
CONVERT(VARCHAR(8), callstartdt, 108)'hh:mm:Ss' gives me the hh:mm:ss part of my DateTime2 cell. However, when I export this output, it's not recognised as either Time or hh:mm:ss format.
E.g. when the output is into Excel, it shows the above as 13:05:22. If I click on the cell in Excel, it says custom format hh:mm:ss. Yet, if I try telling excel count how many cells are greater that 08:00:00, it returns 0, even though I can see there are hundreds. I've double checked the excel cell formats and no matter what I do, it won't see it as hh:mm:ss. I think I need to do something to the SQL first?
Thanks.
March 4, 2016 at 5:38 am
Excel is very particular and somewhat peculiar about dates and times. It tries to "help you" with data by converting it to what it sees as the data you're trying to present. As a simple example, if you enter 00000001 into a cell, Excel automatically coverts that to a 1 for you. The cell format is what determines how it stores and shows it. If you enter '00000001 into the same cell, it'll store it as a string.
How Excel interprets dates and times depends on the version of Excel you're using. If you want to determine if a datetime column is after 8:00 on a certain date, I would just do it in SQL and then you'll already have your answer when you export it to Excel.
March 4, 2016 at 5:50 am
Many thanks for that. It's a bit of a pain sometimes outputting to Excel. I'm using 2010 at present.
So perhaps I could do something like this in the SQL to make it easier with Excel problems:
CASE WHEN CONVERT(VARCHAR(8), MyDateTime2, 108)'hh:mm:Ss' Between 08:00:00 AND 08:30 THEN 1 WHEN CONVERT(VARCHAR(8), MyDateTime2, 108)'hh:mm:Ss' Between 08:00:00 AND 08:30:00 then 2...
and so on. I need to work out how many occurred in each time period so I could use this is my starting point.
March 4, 2016 at 6:03 am
What you say "in each time period" are you looking to get a count of rows by hour? If so, will something like this work for you?
SELECT DATEPART(hour, datetime_column), COUNT(*)
FROM dbo.table_name
GROUP BY DATEPART(hour, datetime_column)
ORDER BY DATEPART(hour, datetime_column);
March 4, 2016 at 6:26 am
I want it to assign a number in a new column if the time was between:
08:00 and 08:15 THEN 1
08:15 and 08:30 THEN 2
etc, and every 1/4 hour up to 5pm.
Once this outputs alongside the other data, I can then complete the other part of my analysis through Excel.
March 4, 2016 at 6:33 am
Take a look at this
select datediff(minute, DATEADD(hour, 8, CAST(CAST(GETDATE() AS DATE) AS DATETIME)), GETDATE())/15
This gives you your number, so calculate 8 am from the date. Note mine is an example and may not be the best way.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply