• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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

0
aneilg
Asked:
aneilg
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
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
 
sameer2010Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
SharathData 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now