特定转换字符串的SQL日期格式 [英] Sql date format for specific convert strings

问题描述

我正在尝试使用101（美国风格）日期格式作为我正在处理的日期维度。目前我有两个公式，我试图用于主要的第一天和月的最后一天，等等。它们显示为YYYY-MM-DD，我希望它们为101（mm / dd / yyyy）。公式是

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

AND

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

我尝试过：



我已经尝试在那里坚持使用101和dateformat并进行更新但是无法弄明白

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

推荐答案

你试过吗？ br />

Have you tried

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

您确实认识到格式化仅适用于字符串中的日期 - 准备以某种形式呈现给用户的日期。您将保留应用于DATE，DATETIME或DATETIME2列的格式，因为日期存储为自特定时间点以来的多个刻度，而不是日期值。

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.

请先阅读我的评论，然后参考这篇文章：在T-SQL中处理自定义日期格式 - SQLServerCentral [ ^ ]



详情请见： CAST和CONVERT（Transact-SQL） - SQL Server Microsoft Docs [ ^ ]

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

这篇关于特定转换字符串的SQL日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！