DATE conversion format in mssql

Question

my hiredate column is in yyyy-mm-dd hh:mm:ss format now i want the output format in DD-Mon-YYYY HR:MM format..

for example :-

hiredate (datatype is varchar) 2018-04-16 12:19:10

required output if 16-Apr-2018 12:20.

Answer 1

Try this

DECLARE @d varchar(50) = '2018-04-16 12:19:10'
SET @d = FORMAT(CONVERT(datetime, @d), 'dd-MMM-yyyy HH:MM', 'en-us')
SELECT @d

or this...

SELECT FORMAT(CONVERT(datetime, '2018-04-16 12:19:10'), 'dd-MMM-yyyy HH:MM', 'en-us')

Result

Answer 2

You need to CAST the field to DATETIME,then use FORMAT function.

There are three parameter in FORMAT function

1. Your datetime [requird]
2. FormatType for this case you need to set dd-MMM-yyyy hh:mm [requird]
3. culture [Optional]

From your question you need to check there are seconds in your datetime.

So add A CASE expression to make it.

DECLARE @Dt VARCHAR(50) = '2018-04-16 12:19:10'

SELECT 
CASE 
  WHEN FORMAT(CAST(@Dt as datetime),'ss') > 0 
  THEN FORMAT(DATEADD(MINUTE,1,CAST(@Dt as datetime)),'dd-MMM-yyyy hh:mm', 'en-US') 
  ELSE FORMAT(CAST(@Dt as datetime),'dd-MMM-yyyy hh:mm', 'en-US')
END

sqlfiddle:http://sqlfiddle.com/#!18/22a72/2

Format

Answer 3

As the comments said, you should import your dates and times into datetime or datetime2 column. However, if you insist on the data as you have it, this is probably closest you can get:

SELECT CONVERT(varchar(50), DATEADD(minute, DATEDIFF(minute, 0, DATEADD(second, 30, CAST('2018-04-16 12:19:10' as datetime2(0)))), 0), 113)
    , CONVERT(varchar(50), DATEADD(minute, DATEDIFF(minute, 0, DATEADD(second, 30, CAST('2018-04-16 12:19:31' as datetime2(0)))), 0), 113);

You could throw in some string manipulation to add your dashes for the date part of it.

Notice, the second CONVERT is for 12:19:31 time which shows the result gets rounded up when needed (in this case to 12:20).

For more CONVERT formats check out this source.

Answer 4

try This

DECLARE @MyDt NVARCHAR(50)='2018-04-16 12:19:10'

SELECT
    InputDt = @MyDt,
    NewDt = REPLACE(LTRIM(RTRIM(CONVERT(CHAR,CAST(@MyDt AS DATETIME),106))),' ','-')
+' '+CAST(CAST(@MyDt AS TIME) AS NVARCHAR(20))

My Result :