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

Question

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?

Microsoft SQL Server 2008 R2. I Have one columns end_date. The second column was a error. — justintime
– justintime, Commented Apr 5, 2018 at 12:50

Jay Shankar Gupta · Accepted Answer · 2018-04-05 13:00:07Z

6

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;

edited Apr 5, 2018 at 13:00

answered Apr 5, 2018 at 12:49

Jay Shankar Gupta

6,1281 gold badge14 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Katusic · Accepted Answer · 2018-04-05 12:57:05Z

1

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

answered Apr 5, 2018 at 12:57

Katusic

845 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related