formating of dates in sql server

Question

This is my query:

SELECT [projectname]
    , CONCAT ([Budget Health]
            , [ProjectBaseline0StartDate
            , [ProjectActualStartDate]
            , [ProjectStartDate]) AS budgethealth
FROM [dbo].[MSP_EpmProject_UserView];

My output is:

projectname budgethealth
------------------------
abc         5% varianceApr  7 2015  8:00AMApr  7 2015  8:00AMApr  7 2015  8:00AM

I want info of budget health in this form:

      <5% variance
      Apr/07/2015
      Apr/08/2015
      Apr/08/2015

NOTE: I want time to be removed and date in this format. plz help.

Possible duplicate of [Unpivot with column name](http://stackoverflow.com/questions/19055902/unpivot-with-column-name) — Evaldas Buinauskas, Oct 07 '15 at 09:29

score 0 · Answer 1 · edited May 23 '17 at 12:14

You can format DateTime as follow:

CONVERT(data_type(length),expression,style)

Ex:

CONVERT(varchar(11),[ProjectBaseline0StartDate])

Full Query:

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
select projectname,CONCAT([Budget Health],@NewLineChar,Convert(varchar(11),[ProjectBaseline0StartDate]),@NewLineChar,CONVERT(varchar(11),[ProjectActualStartDate]),@NewLineChar,CONVERT(varchar(11),[ProjectStartDate])) as budgethealth from [dbo].[MSP_EpmProject_UserView]

Please look for detailed information and style options: http://www.w3schools.com/sql/func_convert.asp

UPDATE

This is the screenshot for the query with convert and default datetime.

UPDATE 2:

If you want to split lines you can use

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

You can't see query result in MSSQL Query Editor and IMHO you don't need it. But if you use this result in your application you'll get the values in new line. Ex:

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL ' + @NewLineChar + 'SELECT SecondLine AS SL')

For more information: New line in Sql Query

are you sure because varchar(11) trims the time part. Added a screenshot for you. — Murat, Oct 07 '15 at 09:52
yes its now working.thanks but i want these dates too in new lines with "/"as a separator — Jatin Khanna, Oct 07 '15 at 10:06

score 0 · Answer 2 · answered Oct 07 '15 at 09:54

as your code and output date format is not in correct way.So I'm Suggesting you to CONCAT with Comma Seperated

so the data will turn into like this

        DECLARE @Table1 TABLE 
        (projectname varchar(5) , budgethealth varchar(200) )
    ;

    INSERT INTO @Table1
        (projectname, budgethealth)
    VALUES ('abc','5% variance,Apr  7 2015  8:00AM,Apr  7 2015  8:00AM,Apr  7 2015  8:00AM')
;WITH CTE AS (
select   projectname,
         Split.a.value('.', 'VARCHAR(100)') AS SubColour  
     FROM  (SELECT [projectname],
             CAST ('<M>' + REPLACE(budgethealth, ',', '</M><M>') + '</M>' AS XML) AS String  
         FROM  @Table1) AS A CROSS APPLY String.nodes ('/M') AS Split(a) )


         Select projectname,
         CASE WHEN CHARINDEX(':',SubColour) > 0 THEN  
         REPLACE(REPLACE(RTRIM(LTRIM(SUBSTRING(SubColour,-1,CHARINDEX(':',SubColour )))),' ','/'),'//','/') 
         ELSE SubColour
             END from CTE

score 0 · Answer 3 · answered Oct 07 '15 at 16:04

Third CONVERT parameter of 107 gives you format "Apr 07, 2015": SELECT CONVERT(varchar(50),[date_column],107)

Then, you can insert "/"'s and remove comma using REPLACE, once for all string.

Final query:

SELECT [projectname],
  [Budget Health] + CHAR(13) + CHAR(10) + 
  REPLACE(REPLACE(
    CONVERT(char(12), [ProjectBaseline0StartDate], 107) + CHAR(13) + CHAR(10) + 
    CONVERT(char(12), [ProjectActualStartDate], 107) + CHAR(13) + CHAR(10) + 
    CONVERT(char(12), [ProjectStartDate], 107),
  ' ', '/'), ',', '') AS budgethealth
FROM [dbo].[MSP_EpmProject_UserView];

formating of dates in sql server

3 Answers3

UPDATE

UPDATE 2: