Click here to Skip to main content
14,173,852 members
Rate this:
 
Please Sign up or sign in to vote.
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)
 
 END
Posted
Updated 20-Apr-17 23:04pm
Comments
NotPolitcallyCorrect 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

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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.
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-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.
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
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)
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
   
v2

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.190524.3 | Last Updated 21 Apr 2017
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