CODEWITHSUNDEEP
sqlsql-server
H. Martin
H. Martin

Reputation: 55

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

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.

Upvotes: 0

Views: 298

Answers (2)

Halim Saad-Rached
Halim Saad-Rached

Reputation: 160

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

sql format tutorial
sql format fiddle

Upvotes: 0

Roshan N
Roshan N

Reputation: 1053

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

Upvotes: 1

Related Questions