English date format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

English date format

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-02-16 : 08:13:03

I am using the following to extract the quarter from a date in my sql 2008 table which is datetime format. I think it is treating dates such as 2010-01-11 00:00:00.000 as 1st November 2010
Do I need to convert the date before I extract the quarter it is in ?

DATEPART(q, ACC_DateofAccident_A)

It is returning the correct quarter in my view but when I use th eview in a crosstab it returns the wrong totals.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-16 : 09:00:42

Can you post the full code you used?

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-02-16 : 09:34:06

Here's my view which returns the correct totals by cause and quarter

SELECT TOP (100) PERCENT ACC_Cause_C AS Cause, DATEPART(q, ACC_DateofAccident_A) AS Qtr, YEAR(ACC_DateofAccident_A) AS Year,
COUNT(ACC_Cause_C) AS Cause_Count
FROM dbo.tblACC_AccidentReport
GROUP BY ACC_Cause_C, DATEPART(q, ACC_DateofAccident_A), YEAR(ACC_DateofAccident_A)
ORDER BY Year, Qtr, Cause

In my Crystal Cross tab I have

Row ACC_Cause_A
Column Year and Qtr
Summarized field Countof cause_Count

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-02-16 : 09:58:06

I think it is becasue I am doing my couinting in the view instead of letting the crosstab do it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources