how to convert date in sql?

Question

1.00/5 (1 vote)

See more:

Im using stored procedure to do some work... My input parameter date format is '2011/01/01' but i want to convert this date format into normal date format like this '01/01/2011'
sample coding:

SQL

create procedure Samp(@dvn int,@fdate date,@tdate date)
as
begin
set @fdate=convert(varchar, getdate(), 103)
set @tdate=convert(varchar, getdate(), 103)
--some coding here
end

then my execution is

exec Samp 40,'01/01/2011','31/01/2011'

Posted 1-May-14 21:09pm

Member 10463904

Updated 1-May-14 21:20pm

Sanjay K. Gupta

v2

Add a Solution

Comments

King Fisher 2-May-14 3:18am

any Error?

Member 10463904 2-May-14 3:37am

s s i got the error..
Msg 8114, Level 16, State 1, Procedure form, Line 0
Error converting data type varchar to date.

King Fisher 2-May-14 7:19am

pass the parameter Like this<br>
exec Samp 40,'2011/01/01','2011/01/01'

'yyyy/mm/dd' or refer solution 2

Sanjay K. Gupta 2-May-14 3:21am

I am not getting your problem. Have you facing any error during execution?

Member 10463904 2-May-14 3:38am

s s when i am executing i got the error as
Msg 8114, Level 16, State 1, Procedure form, Line 0
Error converting data type varchar to date.

6 solutions

Solution 4

Date is a date - no matter of displayed format.
Do not change it or do not store it as a varchar data type. To display it in custom format, please, refer solution 1 by AbhinavS.

Posted 1-May-14 22:12pm

Maciej Los

Comments

Abhinav S 2-May-14 4:15am

Yep. 5.

Maciej Los 2-May-14 4:18am

Thank you, Abhinav ;)

Solution 1

Use CONVERT - http://msdn.microsoft.com/en-us/library/ms187928.aspx[^].
You can choose whatever format you require.

Posted 1-May-14 21:20pm

Abhinav S

Comments

Maciej Los 2-May-14 4:13am

+5!
Some additional info has been posted in my answer ;)

Abhinav S 2-May-14 4:15am

Thank you.

Solution 5

refer this site, I think it will help u..

http://www.w3schools.com/sql/func_convert.asp[^]

Posted 1-May-14 23:00pm

Kan07

Solution 6

-- Day first
SELECT CONVERT(VARCHAR(12),GETDATE(), 103) -- 25/11/2011
SELECT CONVERT(VARCHAR(12),GETDATE(), 105) -- 25-11-2011
SELECT CONVERT(VARCHAR(12),GETDATE(), 104) -- 25.11.2011
SELECT CONVERT(VARCHAR(12),GETDATE(), 106) -- 25 Nov 2011

-- Month first
SELECT CONVERT(VARCHAR(12),GETDATE(), 101) -- 11/25/2011
SELECT CONVERT(VARCHAR(12),GETDATE(), 110) -- 11-25-2011
SELECT CONVERT(VARCHAR(12),GETDATE(), 100) -- Nov 25 2011
SELECT CONVERT(VARCHAR(12),GETDATE(), 107) -- Nov 25, 2011

-- Year first
SELECT CONVERT(VARCHAR(12),GETDATE(), 102) -- 2011.11.25
SELECT CONVERT(VARCHAR(12),GETDATE(), 111) -- 2011/11/25
SELECT CONVERT(VARCHAR(12),GETDATE(), 112) -- 20111125

-- Time only
SELECT CONVERT(VARCHAR(12),GETDATE(), 108) -- 06:21:42
SELECT CONVERT(VARCHAR(12),GETDATE(), 114) -- 06:21:51:610

--Only One
SELECT DATENAME(MONTH, GETDATE()) -- November
SELECT DATENAME(DAY,GETDATE()) -- 25
SELECT DATENAME(YEAR, GETDATE()) -- 2014

Posted 1-May-14 23:32pm

Amalraj Ramesh

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)

King Fisher · Accepted Answer · 2014-05-01T21:22:00

Solution 2

SQL

alter procedure Samp(@dvn int,@fdate varchar(10),@tdate varchar(10))
as
begin
set @fdate=convert(varchar, getdate(), 103)
set @tdate=convert(varchar, getdate(), 103)

select @fdate
Select @tdate
end

Posted 1-May-14 21:22pm

King Fisher

Comments

Maciej Los 2-May-14 4:15am

?<br>
fdate and tdate are input parameters. Why to change its original values inside SP?

King Fisher 2-May-14 5:06am

he is passing the parameter as varchar

exec Samp 40,'01/01/2011','31/01/2011'

Abhinav S 2-May-14 4:16am

5.

King Fisher 2-May-14 5:08am

Thank you ;)

José Amílcar Casimiro · Accepted Answer · 2014-05-01T21:26:00

Solution 3

Hello,

Use 'SET DATEFORMAT dmy;' command.

Please review the documentation: SET DATEFORMAT (Transact-SQL)

Command:
-----------------------------------------
set dateformat dmy;
exec Samp 40,'01/01/2011','31/01/2011'
-----------------------------------------

JAFC

Posted 1-May-14 21:26pm

José Amílcar Casimiro

Updated 1-May-14 21:28pm

v2

Comments

Abhinav S 2-May-14 4:16am

5.

José Amílcar Casimiro 2-May-14 6:55am

Thx

Maciej Los 2-May-14 4:17am

Be careful with SET DATEFORMAT command. It changes date format for server, but does nothing with UI. It is useful when conversion between input format and server format is needed. A4!
Please, see my answer.

José Amílcar Casimiro 2-May-14 6:53am

The requirement set by the user, just wanted to run the stored procedure in a particular date format.
Changing the date format is only valid for the current user session.