CODEWITHSUNDEEP
sqlsql-servert-sql
Si8
Si8

Reputation: 9235

How to format date to a certain way in SQL

I have the following query:

SELECT DATEADD(DAY, 0 - (DATEPART(weekday, GETDATE()) % 7), GETDATE())

Which displays:

2014-04-19 10:47:46.790

How can I modify the query so it displays 04/19/2014 or 04-19-2014

Upvotes: 1

Views: 112

Answers (4)

D Stanley
D Stanley

Reputation: 152634

Date formatting is generally the responsibility of the presentation layer, not the data layer. DateTime doesn't have a format - it's literally a representation of a date and time of day. When you display the DateTime is when you choose the format (if you don't choose one, the system will choose one by default).

You could convert the DateTime to a varchar, but if the value is getting consumed by some other system then I would strongly recommend leaving it as a DateTime and letting the part of the system that displays the date convert it appropriately. Otherwise, if you need to do any kind of date math/comparison/etc., the system is going to have to convert back to a date value which could cause problems.

Based on your subsequent comment I would format the date in the SSRS report appropriately and let the query produce the data value as-is.

Upvotes: 3

Tom
Tom

Reputation: 7740

SELECT CONVERT(VARCHAR, DATEADD(DAY, 0 - (DATEPART(weekday, GETDATE()) % 7), GETDATE()), 101)

Outputs

04/19/2014

And

SELECT CONVERT(VARCHAR, DATEADD(DAY, 0 - (DATEPART(weekday, GETDATE()) % 7), GETDATE()), 110)

Outputs

04-19-2014

Alternately, you could format this in the consuming application (in your case, SSRS). This could be done like this 

=Format(date_column, "MM/dd/yyyy")

or

=Format(date_column, "MM-dd-yyyy")

Upvotes: 3

user3401335
user3401335

Reputation: 2405

for 04/22/2014

select convert(varchar(10), getdate(), 101)

for 04-22-2014

select convert(varchar(10), getdate(), 110)

Upvotes: 4

Anthony Horne
Anthony Horne

Reputation: 2522

I use this to show date only portion:

SELECT CONVERT(VARCHAR(12), DATEADD(DAY, 0 - (DATEPART(weekday, GETDATE()) % 7), GETDATE()), 110)

Will that work for you?

Upvotes: 2

Related Questions