How to change date format in sql procedure .

Question

0.00/5 (No votes)

See more:

my code is...

SQL

Select em.Name, DATEADD(YY, DATEPART(YYYY, getdate()) - DATEPART(YYYY,Marriagedate), Marriagedate) AS Current_data, Marriagedate= DATEDIFF(YEAR,Marriagedate,GETDATE()) from EmployeeMaster em inner join EmpMarriageDate ed on em.Id=ed.EmployeeId
where
 CONVERT(datetime, CAST(MONTH(Marriagedate) AS VARCHAR)  +
						'/' + CAST(DAY(Marriagedate)AS VARCHAR) +
						'/' + CASE WHEN MONTH(Marriagedate) = 1 AND MONTH(GETDATE()) = 12 THEN CAST(YEAR(GETDATE()) + 1 AS VARCHAR) ELSE CAST(YEAR(GETDATE()) AS VARCHAR) END )
					BETWEEN GETDATE() AND DATEADD(dd, 7, GETDATE())

my output is look like 23-12-2015 00:00:00:000
But
i want output only 23-12-2015

please help me solve this question.
Thanks

Posted 23-Dec-15 0:17am

Harish-Sangwan

Updated 23-Dec-15 0:21am

v2

Add a Solution

2 solutions

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)

Kornfeld Eliyahu Peter · Answer 1 · 2015-12-23T00:37:00

You do the old and common mistake of all...
What you see is only a visual representation of the value stored in the DB...
So to change it you first have to decide where do you want to display it...
to taste it run these lines:

C#

Debug.WriteLine(DateTime.Now.ToString("F"));
Debug.WriteLine(DateTime.Now.ToString("ddd, MMM yyyy"));
Debug.WriteLine(DateTime.Now.ToString("dd/MM/yyyy"));

CHill60 · Answer 2 · 2015-12-23T01:52:00

Based on the other question you posted ...
I commented there that you should only format a date when you are presenting it to the user in the GUI layer - this is what Kornfeld Eliyahu Peter is presenting in Solution 1.

However your other question seemed to imply that you were trying to find rows where the next marriage anniversary was within the next 7 days.
The way you are formatting the data to do the WHERE clause is messy and unnecessary.

Consider using something like this instead:

SQL

Select 
	em.Name, 
	DATEADD(YY, DATEPART(YYYY, getdate()) - DATEPART(YYYY,Marriagedate), Marriagedate) AS Current_data, 
	DATEADD(YY, DATEPART(YYYY, getdate()) - DATEPART(YYYY,Marriagedate) + 1, Marriagedate) AS Next_Anniv, 
	HowLongMarried = DATEDIFF(YEAR,Marriagedate,GETDATE()) 
from 
	EmployeeMaster em 
	inner join EmpMarriageDate ed on em.Id=ed.EmployeeId
where 
	DATEADD(YY, DATEPART(YYYY, getdate()) - DATEPART(YYYY,Marriagedate), Marriagedate)
		BETWEEN GETDATE() AND DATEADD(dd, 7, GETDATE())	-- next anniversary is this year
OR
	DATEADD(YY, DATEPART(YYYY, getdate()) - DATEPART(YYYY,Marriagedate) + 1, Marriagedate)
		BETWEEN GETDATE() AND DATEADD(dd, 7, GETDATE())	-- next anniversary is next year

You can make it even clearer by using a CTE ...

SQL

with BaseData AS
(
	Select 
	em.Name, 
	DATEADD(YY, DATEPART(YYYY, getdate()) - DATEPART(YYYY,Marriagedate), Marriagedate) AS Current_data, 
	DATEADD(YY, DATEPART(YYYY, getdate()) - DATEPART(YYYY,Marriagedate) + 1, Marriagedate) AS Next_Anniv, 
	HowLongMarried = DATEDIFF(YEAR,Marriagedate,GETDATE()) 
from 
	EmployeeMaster em 
	inner join EmpMarriageDate ed on em.Id=ed.EmployeeId
)
SELECT [Name], Current_data, HowLongMarried
FROM BaseData
where 
	Current_data BETWEEN GETDATE() AND DATEADD(dd, 7, GETDATE())	-- next anniversary is this year
	OR
	Next_Anniv BETWEEN GETDATE() AND DATEADD(dd, 7, GETDATE())	-- next anniversary is next year

Note that if Marriagedate column has been defined as a DateTime rather than Date you can cast the results to Date

SQL

CAST(Current_data as date)