SQL date formatting

Hey all

I am trying to format my date to

"Monday 21/12/2010" or "DAY dd/MM/YY"


Can I do that in SQL?
samelaminAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MuffyBunnyConnect With a Mentor Commented:
This will work on any datetime value. Just replace the getdate() with the name of your date variable. If you want to create a whole new variable to use after reformatting...

DECLARE @FormattedDateString VARCHAR(30)
SELECT @FormattedDateString =
CASE DATEPART(WEEKDAY,getdate())
      WHEN 1 THEN 'Sunday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 2 THEN 'Monday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 3 THEN 'Tuesday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 4 THEN 'Wednesday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 5 THEN 'Thursday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 6 THEN 'Friday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 7 THEN 'Saturday ' + CONVERT(varchar(10), GETDATE(), 103)
END
0
 
MuffyBunnyCommented:
Do you need the output to remain datetime data type or is it ok for the value to be a string?
0
 
MuffyBunnyCommented:
If a string is ok, this will do it

SELECT
CASE DATEPART(WEEKDAY,getdate())
      WHEN 1 THEN 'Sunday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 2 THEN 'Monday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 3 THEN 'Tuesday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 4 THEN 'Wednesday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 5 THEN 'Thursday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 6 THEN 'Friday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 7 THEN 'Saturday ' + CONVERT(varchar(10), GETDATE(), 103)
END AS formatteddate
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
samelaminAuthor Commented:
Muffy thanks thats good but what if I am passing it a date. This only works for todays date right

so what if I am passing it a date via a parameter will it still work?
0
 
Ephraim WangoyaCommented:
Just use the date parameter or field

CONVERT(varchar(10), @MyDateParam, 103)

or

CONVERT(varchar(10), MyDateField, 103)
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
samelaminAuthor Commented:
Thanks it worked Muffy
0
 
samelaminAuthor Commented:
Thanks mate
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.