CODEWITHSUNDEEP
sql-server-2008
Semil Sebastian
Semil Sebastian

Reputation: 519

How to concatenate Date and DateName together

I want to concatenate Date And Date Name Together. How Can I do it. I need output like

2015-10-09 Friday

I got the DateName=  `SELECT DATENAME(dw,'2015-10-09') as MyDateName`

Is it possible to make it in a single query?

Upvotes: 1

Views: 593

Answers (3)

BalajiShriram
BalajiShriram

Reputation: 1

You can try this code:

SELECT 
    CAST(CAST(GETDATE() AS DATE) AS VARCHAR) + ' ' + DATENAME(DW, GETDATE()) AS [DATE_WITH_WEEKNAME]

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

Try this:

select CAST('2015-10-09' as varchar(10)) + ' ' +DATENAME(dw,'2015-10-09')

SQLFIDDLE DEMO

If your date is already in the varchar format then you dont need to CAST the date and simply try to concatenate them using +

select '2015-10-09' + ' ' +DATENAME(dw,'2015-10-09')

Upvotes: 2

Bridge
Bridge

Reputation: 30651

Since the value you provided is already a string, and DATENAME also returns a string, you can just concatenate together with +:

SELECT '2015-10-09 ' + DATENAME(WEEKDAY,'2015-10-09') as MyDateName

Assuming that in reality this is a parameter or a column of datatype datetime, you'll need to convert it to varchar first. There is no built in style for converting to varchar which contains the Day name, so you'll have to do the work yourself in two parts.

See MSDN page on cast and convert for the built in formats.

For the sake of my example, I'll create a datetime variable to use for tests:

DECLARE @d DATETIME; SET @d = '20151009';

Then to convert it to VARCHAR:

For the date part, ODBC is closest to the format you've asked for with the format yyyy-mm-dd hh:mi:ss(24h). So to get the date in that format as a varchar, you can use CONVERT with that style, value, 20.

SELECT CONVERT(VARCHAR(10), @d, 20)

Note I have converted it to VARCHAR(10) to truncate the timepart of the datetime that you don't want in the output. Then if you concatenate this with a space and the day name you already worked out, you can get your output:

SELECT CONVERT(VARCHAR(10), @d, 20) + ' ' + DATENAME(WEEKDAY, '2015-10-09') AS MyDateName

(Replace my variable with a column name and add a FROM statement if you're after output from a table)

Upvotes: 1

Related Questions