How can I change this type of format date

Question

1.00/5 (2 votes)

See more:

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:

SQL

<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)
 
 END

Posted 20-Apr-17 9:40am

Learn.net37

Updated 20-Apr-17 23:04pm

Add a Solution

Comments

[no name] 20-Apr-17 15:47pm

https://www.codeproject.com/Questions/1183013/Convert-from-gregorian-to-hijri-date

Learn.net37 20-Apr-17 16:22pm

i already deleted but won't delete and its different question if want help just help , i don't understand u don't want us to learn or what , when i post question 80% no answers and when i did some mistake people like show from nowhere just to post this

thank u for let me hate this sh*t

1 solution

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)

CHill60 · Accepted Answer · 2017-04-20T23:04:00

If 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.

SQL

CAST(LicenseExpiry AS DATE)

Here's an example of what I mean:

SQL

declare @demo DateTime = GetDate()
select @demo	           --2017-04-21 09:23:45.753
select CAST(@demo AS date) -- 2017-04-21

For 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.

SQL

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 is

SQL

CREATE 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 0

Then 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)

SQL

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):

SQL

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