Return a string representing the day of the week

[Bobwhosmiles]

Hi All,

New to the forum and new to T-SQL

I'm trying to add a column to the results of query that shows a text string representing the day of the week.

Select Activity.StartDateTime as DAY

...returns the full date and time. I just want the day of the week.

I know it's possible to return an integer which represent the day of the week, and I know there's a way to specify which day of the week you want to call the first. Typically in the US the first day of the week is Sunday I think, but here in the UK it's Monday.

Thing is, I don't just need an integer, I need a string that represents the abbreviated form of the actual name of the day.

eg

MON = Monday
TUE = Tuesday

etc etc

Can anyone help please? Don't know where to start.

Bob

[Bobwhosmiles]

After much hunting around online I've found the DATENAME function which apparently when used with "weekday" as per the example below returns a string like "Sunday", "Monday" or whatever based on the day of the week that correlates to the date

DATENAME(weekday, Activity.StartDateTime)

However, now that I'm at home (not work where I have enterprise manager) I can't test this. Ideally I want to return the first three letters of the string and capitalize them. I'm guessing for the first three letters I could adapt the code above thus:

SELECT LEFT(DATENAME(weekday, Activity.StartDateTime),3) As DAY

Anyone any ideas firstly if this will work and secondly if there's an equivalent to excel/vba's UPPERCASE to capitalize the string?

Any help would be appreciated. :)

Bob

[Bobwhosmiles]

Maybe someone could paste...

SELECT LEFT(DATENAME(weekday, GETDATE()),3) As DAY

...into Query Analyzer and tell me what they get (should be the first three letters of the name of the current day of the week)

[Bobwhosmiles]

For anyone else wanting to do the same - it does work!

Thanks for all your help everyone!

[Tuesday Frase]

Thanks - this was something I needed, but I wasn't aware that it existed!

[ck9663]

As always, these kinds of things is always recommended to be done on your front-end. Unless of course this is some sort of data extraction.

In any case, glad it worked.

Happy Coding!!!

~~ CK