Convert datetime to varchar in sql

Question

0.00/5 (No votes)

See more:

Hello Friends,

I have a stored procedure in which I am passing string ("2011-12-23 00:00:00"), however the column with which i am trying to compare it with has DateTime datatype.

I need to convert either of them.
So I tried -- CONVERT(DATETIME,@date,120) but I am getting error as "Conversion failed when converting datetime from character string."

whats wrong with the query?

SQL

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(  
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	VARCHAR=NULL,
	 @EndDate VARCHAR=NULL
) 
AS  

BEGIN  
	SELECT Id, Username, SenderAddress, ReceiverAddress, AddedOn, ModifiedOn
	FROM Requests
	WHERE
		EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=(CASE WHEN @RequestStatusId IS NULL THEN RequestStatusId ELSE @RequestStatusId END)
		AND AddedOn	LIKE (CASE WHEN CONVERT(DATETIME,@StartDate,120) IS NULL THEN AddedOn ELSE CONVERT(DATETIME,@StartDate,120) END)	
END

Posted 29-Dec-11 23:54pm

tejashri.gandhi

Updated 30-Dec-11 0:07am

Mehdi Gholam

v2

Add a Solution

Comments

anushripatil 30-Dec-11 5:59am

1) try to pass datetime from ur code.
Else
PLease find the links
http://www.sqlusa.com/bestpractices/datetimeconversion/
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25080

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

Wendelius · Answer 1 · 2011-12-30T00:05:00

Solution 2

IMO the best option would be to use datetime data type for your parameters. This way you wouldn't need to do any conversions at db. Conversions may still be needed at client side but then different locales can be properly taken into account. So the procedure could be:

SQL

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(  
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	datetime=NULL,
	 @EndDate datetime=NULL
) 
AS 
...

Posted 30-Dec-11 0:05am

Wendelius

Comments

tejashri.gandhi 30-Dec-11 6:10am

Thanks,
Thats the best option, however can you please guide me to convert string to datetime in asp.net in format yyyy-MM-dd?

I tried using DateTime.ParseExact() funtion in asp.net but its not working.
Thanks

Wendelius 30-Dec-11 6:21am

Have you tried using http://msdn.microsoft.com/en-us/library/ch92fbc1.aspx[^]

Prasad_Kulkarni 30-Dec-11 6:25am

Use this; this will work for sure..
1. SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]
2. SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
3. SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]

Sant Osha · Answer 2 · 2011-12-30T00:52:00

Solution 6

USE this code:

CONVERT(VARCHAR(20),@date,107) AS Date

Regards

Posted 30-Dec-11 0:52am

Sant Osha

Prasad_Kulkarni · Answer 3 · 2011-12-30T00:33:00

Try this, it because of size of datetime..

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(  
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	VARCHAR=NULL,
	 @EndDate VARCHAR=NULL
) 
AS  
 
BEGIN  
	SELECT Id, Username, SenderAddress, ReceiverAddress, AddedOn, ModifiedOn
	FROM Requests
	WHERE
		EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=(CASE WHEN @RequestStatusId IS NULL THEN RequestStatusId ELSE @RequestStatusId END)
		AND AddedOn	BETWEEN 
		CONVERT(VARCHAR(50),@StartDate,113)+'00:00:00',120) 
		AND
		CONVERT(VARCHAR(50),@EndDate,113)+'23:59:59',120)	
END

Anil Avhad [IND] · Answer 4 · 2012-01-01T22:54:00

if you compare date then Please refer following code:

declare @strdate1 varchar(50)
declare @strdate2 varchar(50)
declare @date1 datetime
declare @date2 datetime
set @strdate1 ='2011-12-21 00:00:00'
set @strdate2 ='2011-12-24 00:00:00'
set @date1 = cast(@strdate1 as datetime)
print @date1
set @date2 = cast(@strdate2 as datetime)
print @date2

if(@date1>@date2)
Begin
print 'A>B'
End
else
Begin
print 'A<B'
End

/////////////

the Above Code will solve your problem use cast for conversion of date

Sarvesh Kumar Gupta · Answer 5 · 2011-12-30T00:12:00

Try this:

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(  
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	datetime=NULL,
	 @EndDate datetime=NULL
) 
AS  
 
BEGIN  
	SELECT Id, Username, SenderAddress, ReceiverAddress, AddedOn, ModifiedOn
	FROM Requests
	WHERE
		EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=(CASE WHEN @RequestStatusId IS NULL THEN RequestStatusId ELSE @RequestStatusId END)
		AND AddedOn	LIKE (CASE WHEN CONVERT(varchar(11),@StartDate,120) IS NULL THEN AddedOn ELSE CONVERT(varchar(11),@StartDate,120) END)	
END

Change Varchar(11//<--Change size based on you)

tejashri.gandhi · Answer 6 · 2011-12-30T00:28:00

now I am trying following query

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(  
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	VARCHAR=NULL,
	 @EndDate VARCHAR=NULL
) 
AS  

BEGIN  
	SELECT Id, Username, SenderAddress, ReceiverAddress, AddedOn, ModifiedOn
	FROM Requests
	WHERE
		EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=(CASE WHEN @RequestStatusId IS NULL THEN RequestStatusId ELSE @RequestStatusId END)
		AND AddedOn	BETWEEN 
		CONVERT(DATETIME,CONVERT(VARCHAR(11),@StartDate,113)+'00:00:00',120) 
		AND
		CONVERT(DATETIME,CONVERT(VARCHAR(11),@EndDate,113)+'23:59:59',120)	
END

if I write

EXEC proc_GetUserRequests 6409,1,1,'2011-12-06'

I get error as
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

What might be going wrong?

Manish Langa · Answer 7 · 2011-12-30T19:22:00

Solution 7

u can use

select cast(Date as varchar(20))

Posted 30-Dec-11 19:22pm

Manish Langa

v2

tejashri.gandhi · Answer 8 · 2012-01-01T18:57:00

Solution 8

I passed date as DateTime and then in my stored procedure i write :

SQL

CONVERT(DATETIME, @StartDate+'00:00:00',120) and
CONVERT(DATETIME,@EndDate+'23:59:59',120)

This is working fine..
Thank you everybody for responses and efforts

Posted 1-Jan-12 18:57pm

tejashri.gandhi

Prasad_Kulkarni · Answer 9 · 2011-12-29T23:58:00

Solution 1

Hi,
you din't given length..

CONVERT(data_type(length),data_to_be_converted,style)

http://msdn.microsoft.com/en-us/library/ms187928.aspx[^]

Posted 29-Dec-11 23:58pm

Prasad_Kulkarni

Updated 30-Dec-11 0:06am

v2

Comments

tejashri.gandhi 30-Dec-11 6:02am

but how can i give length to DATETIME datatype?
I tried following query and I am still getting same error

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(
@EmpUserId INT,
@RequestTypeId INT,
@RequestStatusId INT=NULL,
@StartDate VARCHAR=NULL,
@EndDate VARCHAR=NULL
)
AS

BEGIN
SELECT Id, Username, SenderAddress, ReceiverAddress, AddedOn, ModifiedOn
FROM Requests
WHERE
EmpUserId = @EmpUserId
AND RequestTypeId=@RequestTypeId
AND RequestStatusId=(CASE WHEN @RequestStatusId IS NULL THEN RequestStatusId ELSE @RequestStatusId END)
AND CONVERT(VARCHAR(50),AddedOn,120)LIKE (CASE WHEN @StartDate IS NULL THEN AddedOn ELSE @StartDate END)
END

Convert datetime to varchar in sql

9 solutions

Solution 2

Solution 6

Solution 5

Solution 9

Solution 3

Solution 4

Solution 7

Solution 8

Solution 1

Add your solution here

Preview 0