Solved

SQL Server 2005 Date and Time Help

Posted on 2009-04-09
7
374 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:jclem1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 5

Accepted Solution

by:
brandonvmoore earned 250 total points
ID: 24113385
In SQL Server you just have to get the part of the date that you want and assume the rest.  In other words if you want the first day of the month from MM-DD-YYYY, you just use the Month() and Year() functions to retrieve those values and add the '01' for the day yourself.

You can use Day(), Month(), Year(), and DatePart().  The only equivelent feature that would take a minimal amount of work to implement would be finding the first day of a week, but that's pretty simple.  There's also a DateName() function to turn the numbers into words.

See: http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx
0
 
LVL 1

Author Comment

by:jclem1
ID: 24115141
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?
0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24119506
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.
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24119534
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24124052
>>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.
0
 
LVL 1

Author Comment

by:jclem1
ID: 24124303
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
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 24124323
Try it this way:
Declare @txtTo datetime
SET @txtTo = '20090401'

select      month(closed) [Month],
            src_sys,
            Count(*) closed
From      mttr_mtd_2008
Where      closed >= @txtTo
Group by
            month(closed), src_sys
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Display Date and Time 7 48
SQL Lag Function DateDiff 2 26
Backing up Large SQL Server VM Best practice [using Veeam Backup] 8 69
Datatable / Dates ? 4 29
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question