CODEWITHSUNDEEP
sqlsql-serverdatetimecasting
justintime
justintime

Reputation: 101

SQL - Converting a string to a date and / or time

I have a table with some rows. what i want is i want to show the sum of a particular field in the last row that will get generate automatically.

My SQL Code:

SELECT  
    Account,
    Amount = SUM(Amount),
    EndDate = CASE WHEN GROUPING(EndDate) = 1 THEN 'Total' ELSE EndDate END
FROM Test.dbo.T_OP
GROUP BY Account, EndDate WITH ROLLUP
HAVING GROUPING(Account) =0;

The Error Message: Error converting a string to a date and / or time.

If I change the else part I get the desired result only my date is not displayed.

The modified code:

SELECT  
    Account,
    Amount = SUM(Amount),
    EndDate = CASE WHEN GROUPING(EndDate) = 1 THEN 'Total' ELSE 'FakeDate' END
FROM Test.dbo.T_OP
GROUP BY Account, EndDate WITH ROLLUP
HAVING GROUPING(Account) =0;

The Output:

Account Amount  EndDate
70000   776,98  FakeDate
70000   776,98  Total
70108   131,8   FakeDate
70108   60,3    FakeDate
70108   101,1   FakeDate
70108   40,8    FakeDate
70108   694,05  FakeDate
70108   1028,05 Total

How can I get that the date is played out correctly?

Upvotes: 0

Views: 149

Answers (2)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

In CASE WHEN Part you are returning a string 'Total' but in ELSE Part you are returning a date type value so how can one column can have two types of data so you need to cast your date to string type as below :

CAST(End_Date AS VARCHAR)

OR

CONVERT(VARCHAR, End_Date , 120)

Correted Query:

SELECT  
    Account,
    Amount = SUM(Amount),
    EndDate = CASE WHEN GROUPING(EndDate) = 1 THEN 'Total' 
              ELSE CAST(End_Date AS VARCHAR) END
FROM Test.dbo.T_OP
GROUP BY Account, EndDate WITH ROLLUP
HAVING GROUPING(Account) =0;

Upvotes: 6

Katusic
Katusic

Reputation: 84

Change this line:

EndDate = CASE WHEN GROUPING(EndDate) = 1 THEN 'Total' ELSE EndDate END

To:

EndDate = CASE WHEN GROUPING(EndDate) = 1 THEN 'Total' ELSE convert(varchar(25), EndDate, 120) END

Upvotes: 1

Related Questions