CODEWITHSUNDEEP
sqlsql-server
Happy
Happy

Reputation: 323

How to merge two aggregate functions in SQL Server

I need to merge two aggregate functions in SQL Server.

Code:

SELECT 
    REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY],
    (count(Sno)+1) AS count 
FROM tbl_demographic_reg

Output:

DDMMYY   count

060114  1

I need the above output to be 06011400001 - how to get it? Thanks

Upvotes: 0

Views: 113

Answers (2)

Sushil
Sushil

Reputation: 196

SELECT 
    REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') +
    RIGHT('0000'+ CONVERT(VARCHAR,count(Sno)),6)
FROM tbl_demographic_reg

Upvotes: 2

Szymon
Szymon

Reputation: 43023

You can just concatenate them together, adding necessary zeroes in between:

SELECT 
    REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') + 
    right('0000' + cast((count(Sno)+1) as varchar(5)), 5)
FROM tbl_demographic_reg

Upvotes: 1

Related Questions