SQL Dates The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

My table is as follows
      [05_Appt Date] [varchar] (50),

My select statement is as follows
dbo.FormatDates(TblApptDetails.ApptConfirmedDate, 'DD/MM/YY') AS [Confirmed Date],      

when i run my query i get

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


ALTER FUNCTION [dbo].[FormatDates]
( 
    @dt DATETIME,
	@format VARCHAR(50) 
) 
RETURNS VARCHAR(64)
 
AS
 
BEGIN
 
    DECLARE @dtVC DATETIME
    SELECT @dtVC = CASE @format 
 
    WHEN 'DD/MM/YY' THEN 
        CONVERT(VARCHAR(50), @dt, 3) 
    ELSE 
        'Invalid format specified' 
    END 
 
    RETURN @dtVC 
 
END

Open in new window

aneilgAsked:
Who is Participating?
 
sameer2010Connect With a Mentor Commented:
Hi,

I just tried this and it worked...
create function DateFunc (@dt datetime, @format varchar(50)) 
returns varchar(64)
as
begin
	declare @dtvc datetime
--	set @dtvc = '12-31-9999'
	select @dtvc = case @format
	when 'dd/mm/yy' then convert(varchar(50),@dt,3)
	else 'invalid format specified'
	end
	return @dtvc
end
;
select dbo.DateFunc('1901-01-01 00:00:00','dd/mm/yy');

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
can you please give us some sample data from the table
0
 
aneilgAuthor Commented:

TblApptDetails.ApptConfirmedDate contains the following data.

1901-01-01 00:00:00

hope this helps.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Aneesh RetnakaranDatabase AdministratorCommented:
are you sure all the values are in that format ?
0
 
sameer2010Commented:
Can you try executing the same by setting dateformat to ymd?
0
 
aneilgAuthor Commented:
thanks i'll give it a try.
0
 
SharathConnect With a Mentor Data EngineerCommented:
>> The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


some of your dates may not in YY/MM/DD format. hence you are getting that error.

Run the below query and check the result. Those dates are not in MM/DD/YYYY or MM/DD/YY format.

select ApptConfirmedDate from TblApptDetails where isdate(ApptConfirmedDate) = 0



0
 
aneilgAuthor Commented:
Thanks for all your help guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.