-3

What is wrong in sql 

CONCAT(title, ', from ', CONVERT(DATETIME,CONVERT(NVARCHAR,startdate,107)), ' to ', CONVERT(DATETIME,CONVERT(NVARCHAR,end_date,107)), ' (', duration , ')')

I want to change datetime to Sep 25, 2018 format

Karen Shahmuradyan
  • 5
  • 5
  • 4
    What database are you using? What does your data look like? What results are you getting? What results do you want to get? – Gordon Linoff Aug 30 '18 at 14:35
  • 1
    The convert with 107 implies SQL Server - but thats being converted back to a datetime, so I suspect the 'wrong' part here is that the formatting on the result is wrong? – Andrew Aug 30 '18 at 14:47

2 Answers2

0
SELECT LEFT(DATENAME(MONTH, GETDATE()), 3) + ' ' + cast(datepart(dd, getdate()) as varchar) + ', ' + cast(datepart(yyyy, getdate()) as varchar)

or

select convert(nvarchar,getdate(),107)
Alex
  • 2,247
  • 1
  • 27
  • 37
  • `select convert(nvarchar,getdate(),107)` would achieve the same in a simpler manner, I don't believe the problem is the original conversion, but the conversion back to datetime he is doing after that. – Andrew Aug 30 '18 at 14:59
0

The question is somewhat unclear, I think your issue lies in that you are converting the date to an nvarchar using the 107 format which is correct for what you posted you wish to do. You then convert it back to a datetime, which reverts it back to the default settings for the date.

So I think you just need to simplify it to:

CONCAT(title, ', from ',CONVERT(NVARCHAR,startdate,107), ' to ',CONVERT(NVARCHAR,end_date,107), ' (', duration , ')')

Original:

select convert(datetime,convert(nvarchar,getdate(),107));

Returns (this is based on local settings, so for the SQL Server I used, it returns this):

2018-08-30 00:00:00.000

Less Conversion Code:

select convert(nvarchar,getdate(),107);

Returns:

Aug 30, 2018
Andrew
  • 26,629
  • 5
  • 63
  • 86