asked on

SQL Server 2005 Date and Time Help

Experts, I am in the process of converting from an Oracle database to SQL server 2005. I previously used TOAD for Oracle as a querying interface which is where the example code below comes from.

My problem is that I can't get the below query to run in my SQL server. The problem is with the truncate function on the date, in oracle I could use trunc(closed,'MON') or trunc (closed,'YYYY') or to_char(closed,'HH') depending on how I wanted to manipulate the closed date of the trouble ticket.

Please tell me there is something equvalent in SQL that gives me the same options and is just as user friendly, if I have to build it as a script or stored procedure or something that is fine. I just need something because I change the date criteria every day in various ad hoc queries depending on what people ask for.

Thanks

select trunc(CLOSED, 'MON')Month,SRC_SYS,vz_svc_type, sum(RESP_DUR/60) Total_Hours , count (1) Reports
from dbo.MTTR_MTD_2008
where trunc(CLOSED, 'Mon') = '01-APR-2009' 
    and VZ_SVC_TYPE != 'UNK'       
group by trunc(CLOSED, 'MON'), SRC_SYS,vz_svc_type

Open in new window

ASKER CERTIFIED SOLUTION

brandonvmoore

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jclem1

ASKER

The (closed) field that I am pulling from is as such (mm-dd-yyyy 12:00 AM), the Trunc function that I used in ORACLE removed the Time and just gave me the month day and year for each record, of course the day was always listed as 1. I could change the output by changing what I put after the field, for example trunc(CLOSED, 'MON') would give me what I just said, if I changed the 'mon' to 'hh' I would get the hour of the day on a 24 hour clock.

So there is nothing in SQL that will do that?

brandonvmoore

I'm afraid not. You're asking for a special function to give you a result that you already know the answer for though (except in the case of the start date of the week, that would be useful). All you have to do is append the time of 12:00 AM yourself.

brandonvmoore

Upon rereading your last comment I'm wondering if we're on the same page or not. Just so that we are on the same page, make sure you check out that link I gave you and check out examples of the functions I mentioned there.

Anthony Perkins

>>So there is nothing in SQL that will do that?<<
If you are looking for a date data type without the time or time data type without a date then you will have to wait until you can upgrade to SQL Server 2008.

jclem1

ASKER

So this is what I am using now and it seems to work, I read the link and comments above. What I think is happening is that it is giving me results by day of the month or something it is not grouping as I would expect it to.

Declare @txtTo varchar (10)
SET @txtTo = '4/1/2009'
select month(closed) as Month, src_sys,Count (1) closed
From mttr_mtd_2008
Where closed >= convert(datetime, @txtTo, 101)
Group by closed,src_sys

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial