Click here to Skip to main content
14,093,107 members
Rate this:
Please Sign up or sign in to 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,

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
Updated 25-Feb-19 5:17am
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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Have you tried
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.
Rate this: bad
Please Sign up or sign in to vote.

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[^]

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190518.1 | Last Updated 25 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100