Sql date format for specific convert strings

Question

1.00/5 (1 vote)

See more:

, +

I am trying to use 101 (US style) date format for my date dimension I am working on. Currently I have two formulas which I am trying to use for the main first day and last day of month, etc. They show up as YYYY-MM-DD and I want them at 101 (mm/dd/yyyy). The formulas are

DATEADD(month, DATEDIFF(month, 0, @CurrentDate), 0) AS first_day_of_month,

AND

CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, @CurrentDate)))) AS last_day_of_month,

What I have tried:

I've tried sticking 101 in there and dateformat and doing an update as well but couldn't figure it out

Posted 25-Feb-19 4:41am

Trogers96

Updated 25-Feb-19 5:17am

Add a Solution

Comments

Maciej Los 25-Feb-19 11:15am

Date is date and nothing else! If you would like to display custom format, you have to convert it into string.

2 solutions

Solution 2

Please, read my comment first, then refer this article: Dealing with custom date formats in T-SQL - SQLServerCentral[^]

For further details, please see: CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Docs[^]

Posted 25-Feb-19 5:18am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2019-02-25T05:16:00

Have you tried

SQL

SELECT FORMAT (GETDATE(), 'MM/dd/yyyy ') AS Date

You do realise that formatting only applies to dates in strings - dates which are prepared for presentation to the user in some form. No formatting you apply to a DATE, DATETIME or DATETIME2 column will be retained as dates are stored as a number of ticks since a specific point in time, not as a "date" value.