Solved

SQL date formatting

Posted on 2011-02-15
8
328 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:samelamin
8 Comments
 
LVL 6

Expert Comment

by:MuffyBunny
ID: 34898468
Do you need the output to remain datetime data type or is it ok for the value to be a string?
0
 
LVL 6

Expert Comment

by:MuffyBunny
ID: 34898553
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
 

Author Comment

by:samelamin
ID: 34898666
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 32

Expert Comment

by:ewangoya
ID: 34898692
Just use the date parameter or field

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

or

CONVERT(varchar(10), MyDateField, 103)
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 34898699
0
 
LVL 6

Accepted Solution

by:
MuffyBunny earned 500 total points
ID: 34898712
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
 

Author Comment

by:samelamin
ID: 34898741
Thanks it worked Muffy
0
 

Author Closing Comment

by:samelamin
ID: 34898744
Thanks mate
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question