如何更改此类型的格式日期 [英] How can I change this type of format date
问题描述
你好，我的列名为LicenseExpiry，条件显示所有日期将在45天后到期，因为你看到我使用hijiri日期也是日期有数据类型（日期），它看起来像这样
05/28/32 12:00:00 AM
这是一个日期，所以我怎么能像28/05/1432或28/05/32一样显示它并删除或隐藏（时间）
请不要参考任何链接，因为如果有人能解决它，我会很感激
我尝试过：
< pre> ALTER PROCEDURE [dbo]。[Cars_E]
- 添加参数此处的存储过程
as
开始
SELECT [Purchesdateen]，[Purchesdate]，[Selleren]，[卖家]，[备注]，[更多信息] ，[总计]，
[Inu rancevalue]，[OtherCharge]，[ShippingCharge]，[PurchesValue]，[SpareKeys]，转换（ varchar ，InsuranceExpiryen， 103 ） as InsuranceExpiryen， convert （ varchar ，InsuranceExpiry， 103 ） as InsuranceExpiry，[InsuranceCompany]，
convert （ varchar ，LicenseExpiryen， 103 ） as LicenseExpiryen，
convert （ date ，[LicenseExpiry]， 103 ） as LicenseExpiry，[Orignalen]，[Orignal]，[Statusen]，[Status]，[Locationen]，[Location]，[Userren]，[Userr]，[Owneren]，
[O wner]，[Colouren]，[CustomerDuties]，[Contractno]，[Startcontract]，[Startcontracten]，[图像]
，[颜色]，[ Yearen]，[Year]，[Typeen]，[ Type ]，[Platenoen]，[Plateno]，[No] FROM [ALL_VECH]
其中 CONVERT （ date ，LicenseExpiry， 103 ）< = CONVERT （ nvarchar （ 10 ），GETDATE（）+ 45， 131 ）
END
< blockquote>如果时间显示，那么数据类型必须是DateTime - 摆脱将其作为日期转换的时间，即
CAST（LicenseExpiry AS DATE ）H.以下是我的意思的一个例子：
声明 @ demo DateTime = GetDate（）
选择 @ demo - 2017-04-21 09：23：45.753
选择 CAST（ @ demo AS 日期） - 2017-04-21 对于检查日期+ 45天，您应该真正使用DATEADD函数而不仅仅是+ 45，并且在比较时绝对不会对日期格式进行任何转换。
WHERE CAST（LicenseExpiry AS date ）< = dateadd（DD， 45 ，getdate（）） 谈到转换日期，在将日期返回到调用进程之前，你不应真正转换日期。以特定格式显示日期应保留在表示层。
它（日期转换）可以真正减慢进程，这意味着调用进程可能必须将varchar转换回日期使用它！
我跟着来自@ NotPoliticallyCorrect评论的链接 - 从gregorian转换为hijri date [ ^ ]似乎SQL Server没有特别好地处理hijiri日期。
过去使用非格里高利日期时使用的一种技术是生成一个永久保留在我的数据库中作为参考的日历表。 />
基本设置为
CREATE TABLE [dbo]。[DateMapper]（
[GregDate] [ date ] NOT NULL ，
[MappedDate] [ date ] NULL ，
[评论] [ nvarchar ]（ 124 ） NULL
） ON [ PRIMARY ]
- 在接下来的10年里用格里高利日期填充表格
; WITH Q AS
（
SELECT CAST（' 2010-JAN-01' AS DATE ） as datum
UNION ALL
SELECT DATEADD（dd， 1 ，datum） FROM Q
WHERE DATEADD（dd， 1 ，基准）< DATEADD（yy， 10 ，GETDATE（））
）
INSERT INTO DateMapper
SELECT datum，CAST（ null AS date ）， null < span class =code-keyword> as [Comments]
FROM Q
OPTION （MAXRECURSION 0 然后你运行任何转换对表中的日期 - 只有一次。显然记得根据需要安排对表的更新。
使用您加入DateMapper表的表来检查您想要的日期（在WHERE子句中）例如，您的选择查询将成为（考虑上述所有评论）
SELEC T [Purchesdateen]，[Purchesdate]，[Selleren]，[卖方]，[Notes]，[MoreInformation]，[Total]，[Inurancevalue]，[OtherCharge]，[ShippingCharge]，[PurchesValue]，[ SpareKeys]，InsuranceExpiryen，InsuranceExpiry，[InsuranceCompany]，LicenseExpiryen，LicenseExpiry，[Orignalen]，[Orignal]，[Statusen]，[Status]，[Locationen]，[Location]，[Userren]，[Userr]，[Owneren] ，
[所有者]，[Colouren]，[CustomerDuties]，[Contractno]，[Startcontract]，[Startcontracten]，[图像]，[颜色] ，[Yearen]，[Year]，[Typeen]，[ Type ]，[Platenoen]，[Plateno]，[No]
FROM [ALL_VECH] AV
INNER JOIN DateMapper DM ON DM.GregDate = CAST（AV.LicenseExpiry AS date ）
WHERE DM.MappedDate< = dateadd（DD ， 45 ，getdate（））
完成所有这些后，我重新阅读了这个问题，我想我误解了它。您可能会发现此代码段更有用（但我也会留下上述建议）：
声明 @demo DateTime = GetDate（）
选择 @ demo - 2017-04-21 09：23：45.753
选择 CAST（ @ demo AS 日期） - 2017-04-21
SELECT CONVERT （ varchar ，CAST（ @ demo AS date ）， 131 ） - 25/07/1438
hello, i have column name LicenseExpiry with condition to show the all the dates will expire in 45 days as u see and i use hijiri date also the date has datatype (date) and its appear like this
05/28/32 12:00:00 AM
this is a hijri date so how can i show it like this 28/05/1432 or 28/05/32 and delete or hide the (time)
please don't refer any links because i read it all if some can solve it i will be appreciate
What I have tried:
<pre>ALTER PROCEDURE [dbo].[Cars_E]
-- Add the parameters for the stored procedure here
as
begin
SELECT [Purchesdateen], [Purchesdate], [Selleren], [Seller], [Notes], [MoreInformation], [Total],
[Inurancevalue], [OtherCharge], [ShippingCharge], [PurchesValue], [SpareKeys], convert(varchar, InsuranceExpiryen, 103) as InsuranceExpiryen,convert(varchar, InsuranceExpiry, 103) as InsuranceExpiry, [InsuranceCompany],
convert(varchar, LicenseExpiryen, 103) as LicenseExpiryen ,
convert(date, [LicenseExpiry], 103) as LicenseExpiry, [Orignalen], [Orignal], [Statusen], [Status], [Locationen], [Location], [Userren], [Userr], [Owneren],
[Owner], [Colouren], [CustomerDuties], [Contractno],[Startcontract], [Startcontracten] , [Image]
, [Colour], [Yearen], [Year], [Typeen], [Type], [Platenoen], [Plateno], [No] FROM [ALL_VECH]
where CONVERT(date,LicenseExpiry,103) <= CONVERT(nvarchar(10) ,GETDATE()+45,131)
ENDIf the time is showing then the data type must be DateTime - to get rid of the time just cast it as a Date i.e.CAST(LicenseExpiry AS DATE)Here's an example of what I mean:
declare @demo DateTime = GetDate() select @demo --2017-04-21 09:23:45.753 select CAST(@demo AS date) -- 2017-04-21For checking a date + 45 days you should really use the DATEADD function rather than just + 45 and definitely don't do any conversions on date format when comparing.
WHERE CAST(LicenseExpiry AS date) <= dateadd(DD, 45, getdate())Talking of converting dates, you shouldn't really be converting the Dates before returning them to the calling process. Displaying dates in a particular format should be left to the presentation layer.
It (conversion of dates) can really slow down processes and it means the calling process may have to convert the varchar back to a date to use it!
I followed the link from @NotPoliticallyCorrect's comment - Convert from gregorian to hijri date[^] and it seems that SQL Server doesn't handle hijiri dates particularly well.
A technique I have used in the past when using non-gregorian dates is to generate a "calendar" table that stays permanently on my database as a reference.
The basic set up isCREATE TABLE [dbo].[DateMapper]( [GregDate] [date] NOT NULL, [MappedDate] [date] NULL, [Comments] [nvarchar](124) NULL ) ON [PRIMARY] -- Populate the table with Gregorian dates for the next 10 years ;WITH Q AS ( SELECT CAST('2010-JAN-01' AS DATE) as datum UNION ALL SELECT DATEADD(dd, 1, datum) FROM Q WHERE DATEADD(dd, 1, datum) < DATEADD(yy, 10, GETDATE()) ) INSERT INTO DateMapper SELECT datum, CAST(null AS date), null as [Comments] FROM Q OPTION (MAXRECURSION 0Then you run whatever the conversion is against the dates in the table - only once. Obviously remember to schedule updates to the table as required in the future.
To use the table you join against the DateMapper table to check which ever dates you want (in the WHERE clause). E.g. Your select query would become (taking into account all of the comments above)SELECT [Purchesdateen], [Purchesdate], [Selleren], [Seller], [Notes], [MoreInformation], [Total], [Inurancevalue], [OtherCharge], [ShippingCharge], [PurchesValue], [SpareKeys], InsuranceExpiryen,InsuranceExpiry, [InsuranceCompany], LicenseExpiryen , LicenseExpiry, [Orignalen], [Orignal], [Statusen], [Status], [Locationen], [Location], [Userren], [Userr], [Owneren], [Owner], [Colouren], [CustomerDuties], [Contractno],[Startcontract], [Startcontracten] , [Image], [Colour], [Yearen], [Year], [Typeen], [Type], [Platenoen], [Plateno], [No] FROM [ALL_VECH] AV INNER JOIN DateMapper DM ON DM.GregDate = CAST(AV.LicenseExpiry AS date) WHERE DM.MappedDate <= dateadd(DD, 45, getdate())
[Edit]
Having done all of that I re-read the question and I think I misunderstood it. You may find this snippet more useful (but I will leave the above advice too):declare @demo DateTime = GetDate() select @demo --2017-04-21 09:23:45.753 select CAST(@demo AS date) -- 2017-04-21 SELECT CONVERT(varchar, CAST(@demo AS date), 131) -- 25/07/1438
这篇关于如何更改此类型的格式日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
