Need to Modify SQL Queries to return different date format

wint100
wint100 used Ask the Experts™
on
I'm using the queries below to return Data from my temptable afet a SP is run.

I need to modify them to return a different date format without affecting the result grouping.

Daily Report: needs to be in format 'Mon 16 Aug 2009' or Mon 16/08/09
Weekly should be same as above
Monthly should be same as above
Yearly should be in format 'Aug 09' instead of the month number. Ideally return a seperate column with new format as well as having the Month number column
--Daily report
DECLARE @Date DATETIME
SET @Date = '20091005'
 
SELECT 
    TimeStamp, DataChange, DataCost 
FROM #ReportData 
WHERE DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) = @Date
ORDER BY TimeStamp
 
--Weekly report
DECLARE @sDate DATETIME
SET @sDate = '20090901'
SELECT 
    DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) AS Date,
    SUM(DataChange) AS DataChange,
    SUM(DataCost) AS DataCost
FROM #ReportData
WHERE 
    DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) >= DATEADD(DD,0, DATEDIFF(DD,0,@sDate))
    AND DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) < DATEADD(DD,7, DATEDIFF(DD,0,@sDate))
GROUP BY DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp))
WITH ROLLUP
 
--Month report
DECLARE @Month DATETIME
SET @Month = 9
DECLARE @Year DATETIME
SET @Year = 2009
 
SELECT 
    DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) AS Date,
    SUM(DataChange) AS DataChange,
    SUM(DataCost) AS DataCost
FROM #ReportData
WHERE 
    DATEPART(YY, TimeStamp) = @Year
    AND DATEPART(MM, TimeStamp) = @Month
GROUP BY DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp))
WITH ROLLUP
 
--Yearly
DECLARE @QueryYear INT
SET @QueryYear = 2009
 
;WITH
cteTally AS
(SELECT TOP (12)
        ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
   FROM Master.sys.All_Columns t1
  CROSS JOIN Master.sys.All_Columns t2
)
SELECT 
    N AS Month,
    SUM(DataChange) AS DataChange,
    SUM(DataCost) AS DataCost
FROM cteTally
LEFT JOIN #ReportData ON MONTH(TimeStamp) = N
WHERE YEAR(TimeStamp) = @QueryYear OR TimeStamp IS NULL
GROUP BY N, DATENAME(MM, TimeStamp), MONTH(TimeStamp)
ORDER BY N

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009
Commented:
Hello wint100,

--Daily report
DECLARE @Date DATETIME
SET @Date = '20091005'
 
SELECT
    LEFT( DATENAME(WEEKDAY, TimeStamp) ,3) +' '+CONVERT (varchar, TimeStamp,03), DataChange, DataCost
FROM #ReportData
WHERE DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp))  = @Date
ORDER BY TimeStamp
 
--Weekly report
DECLARE @sDate DATETIME
SET @sDate = '20090901'
SELECT
    LEFT( DATENAME(WEEKDAY, TimeStamp) ,3) +' '+CONVERT (varchar, TimeStamp,03) AS Date,
    SUM(DataChange) AS DataChange,
    SUM(DataCost) AS DataCost
FROM #ReportData
WHERE
    DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) >= DATEADD(DD,0, DATEDIFF(DD,0,@sDate))
    AND DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) < DATEADD(DD,7, DATEDIFF(DD,0,@sDate))
GROUP BY LEFT( DATENAME(WEEKDAY, TimeStamp) ,3) +' '+CONVERT (varchar, TimeStamp,03)
WITH ROLLUP
 
--Month report
DECLARE @Month DATETIME
SET @Month = 9
DECLARE @Year DATETIME
SET @Year = 2009
 
SELECT
    LEFT( DATENAME(WEEKDAY, TimeStamp) ,3) +' '+CONVERT (varchar, TimeStamp,03) AS Date,
    SUM(DataChange) AS DataChange,
    SUM(DataCost) AS DataCost
FROM #ReportData
WHERE
    DATEPART(YY, TimeStamp) = @Year
    AND DATEPART(MM, TimeStamp) = @Month
GROUP BY LEFT( DATENAME(WEEKDAY, TimeStamp) ,3) +' '+CONVERT (varchar, TimeStamp,03)
WITH ROLLUP
 
--Yearly
DECLARE @QueryYear INT
SET @QueryYear = 2009
 
;WITH
cteTally AS
(SELECT TOP (12)
        ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
   FROM Master.sys.All_Columns t1
  CROSS JOIN Master.sys.All_Columns t2
)
SELECT N,
    RIGHT( convert(varchar, timestamp, 06),6)  ,
    SUM(DataChange) AS DataChange,
    SUM(DataCost) AS DataCost
FROM cteTally
LEFT JOIN #ReportData ON MONTH(TimeStamp) = N
WHERE YEAR(TimeStamp) = @QueryYear OR TimeStamp IS NULL
GROUP BY N, RIGHT( convert(varchar, timestamp, 06),6)  
ORDER BY N

Regards,

Aneesh

Author

Commented:
Sorry, the Day report needs to be in time format '08:00:00'

Author

Commented:
Also, the Year query is deisgned to return the date even when no data is present, with a 0 in the data and cost columns. So I will need the same from the new query please. With date returned for every month.
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Interesting construction of the months list.

I would use following code (month number from the last query can be removed later):

--Daily report
DECLARE @Date DATETIME
SET @Date = '20091005'
 
SELECT 
    LEFT(Datename(WeekDay, TimeStamp), 3) + ' ' + convert(varchar(12), TimeStamp,106) AS Date, 
      DataChange, DataCost 
FROM #ReportData 
WHERE DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) = @Date
ORDER BY TimeStamp
 
--Weekly report
DECLARE @sDate DATETIME
SET @sDate = '20090901'
SELECT 
    LEFT(Datename(WeekDay, TimeStamp), 3) + ' ' + convert(varchar(12), TimeStamp,106) AS Date, 
    SUM(DataChange) AS DataChange,
    SUM(DataCost) AS DataCost
FROM #ReportData
WHERE 
    DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) >= DATEADD(DD,0, DATEDIFF(DD,0,@sDate))
    AND DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) < DATEADD(DD,7, DATEDIFF(DD,0,@sDate))
GROUP BY LEFT(Datename(WeekDay, TimeStamp), 3) + ' ' + convert(varchar(12), TimeStamp,106)
WITH ROLLUP
 
--Month report
DECLARE @Month DATETIME
SET @Month = 9
DECLARE @Year DATETIME
SET @Year = 2009
 
SELECT 
    LEFT(Datename(WeekDay, TimeStamp), 3) + ' ' + convert(varchar(12), TimeStamp,106) AS Date,
    SUM(DataChange) AS DataChange,
    SUM(DataCost) AS DataCost
FROM #ReportData
WHERE 
    DATEPART(YY, TimeStamp) = @Year
    AND DATEPART(MM, TimeStamp) = @Month
GROUP BY LEFT(Datename(WeekDay, TimeStamp), 3) + ' ' + convert(varchar(12), TimeStamp,106)
WITH ROLLUP
 
--Yearly
DECLARE @QueryYear INT
SET @QueryYear = 2009
 
SELECT * from sys.All_columns
 
;WITH
cteTally AS (
SELECT 1 AS N, 'Jan' AS Mname UNION ALL SELECT 2, 'Feb' UNION ALL SELECT 3, 'Mar' UNION ALL SELECT 4, 'Apr' UNION ALL 
SELECT 5 AS N, 'May' AS Mname UNION ALL SELECT 6, 'Jun' UNION ALL SELECT 7, 'Jul' UNION ALL SELECT 8, 'Aug' UNION ALL 
SELECT 9 AS N, 'Sep' AS Mname UNION ALL SELECT 10,'Oct' UNION ALL SELECT 11,'Nov' UNION ALL SELECT 12,'Dec' 
)
SELECT N,
    Mname + ' ' + STR(@QueryYear) AS Month,
    SUM(DataChange) AS DataChange,
    SUM(DataCost) AS DataCost
FROM cteTally
LEFT JOIN #ReportData ON MONTH(TimeStamp) = N
WHERE YEAR(TimeStamp) = @QueryYear OR TimeStamp IS NULL
GROUP BY N, Mname + ' ' + STR(@QueryYear)
ORDER BY N

Open in new window

It seems Anesh is much faster...

The daily report can contain both date and time:

SELECT
    LEFT(Datename(WeekDay, TimeStamp), 3) + ' ' + left(convert(varchar, TimeStamp,113), 20) AS Date,
      DataChange, DataCost
FROM #ReportData
WHERE DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) = @Date
ORDER BY TimeStamp

Author

Commented:
Should the new year query retun all month names and number?
The month number is necessary for ORDER BY clause in my query.

Author

Commented:
Month number is fine, but I also need the month name for every month regardless if data is present for the month or not.
Yes, the month name (and year) is present in both solutions.

Author

Commented:
Can the daily be formatted to only contain time? The Year now works well, was the 'SELECT * from sys.All_columns' part needed? AS this returned another table before the one I needed to my web app got confused.
Daily report containing just the time:

SELECT
    convert(varchar, TimeStamp,108) AS Time,
    DataChange, DataCost
  FROM #ReportData
 WHERE DATEADD(DD,0, DATEDIFF(DD,0,TimeStamp)) = @Date
 ORDER BY TimeStamp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial