Is there a way to get the day of the week, month and year in one statement?-CodePudding

Using Microsoft server management studio. I am currently getting what I need by using multiple statements but I was wondering if there was a better cleaner way to do it. I need to get the day of the month, the day name (Monday), month, and the year.

  DATENAME(WEEKDAY, f.DepartDateTime)   ', '  

  DATENAME(MONTH, f.DepartDateTime)   ' '  

  DATENAME(DAY , f.DepartDateTime)   

  IIF(DATENAME(DAY, f.DepartDateTime) IN (1,21,31), 'st', 
   
IIF(DATENAME(DAY, f.DepartDateTime) IN (2,22), 'nd', 
  
IIF(DATENAME(DAY, f.DepartDateTime) IN (3,23), 'rd', 'th')))   ', '  

DATENAME(YEAR, f.DepartDateTime)   ' at '  

DATENAME(HOUR, f.DepartDateTime)    ':'  

DATENAME(MINUTE, f.DepartDateTime)   '. '

I guess i'm wondering if there is a function, like DATENAME that will take more than one parameter at a time.

This is what i have and it works, but i'm still learning and was wondering if there was a better way. !!This is for a past homework assignment.

CodePudding user response：

You can use convert function to get different output format as below.

select CONVERT(varchar(20),getdate(),101)
select CONVERT(varchar(20),getdate(),106)

For more format options refer the link

CodePudding user response：

SELECT FORMAT (f.DepartDateTime, 'dddd, dd-MMM-yyyy') as FormattedDepartDate

sql format tutorial
sql format fiddle