SQL Server date error

Question

I try to execute the query below but I got an error saying"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". I try to convert the date and cast the date but same error will comes out.

select DATEDIFF(YEAR,cast(Startdate as datetime),isnull(cast(Enddate as datetime), GETDATE())) 
from employmentdetails where id=36

select DATEDIFF(YEAR,CONVERT(varchar,Startdate,101),isnull(CONVERT(varchar,Enddate,101),CONVERT(varchar,GETDATE(),101))) 
from employmentdetails where id=35


create table EmploymentDetails(
  Id                  bigint         not null identity constraint PK_EmploymentDetails primary key,
  aId                 bigint         not null,
  Startdate           varchar(25),
  Enddate             varchar(25),
  Position            varchar(30),
  PositionLevelId     bigint         not null,
  SpecializationId    bigint         not null,
  PositionId          bigint         not null,
  StartSalary         varchar(50),
  EndSalary           varchar(50),
  DescriptionofDuties nvarchar(1000),
  ReasonforLeaving    nvarchar(200),
  CompanyName         nvarchar(100),
  TypeofBusiness      varchar(50),
  Address1            varchar(25),
  Address2            varchar(25),
  City                varchar(25),
  Province            varchar(25),
  StateorRegion       varchar(25),
  CountryId           bigint,
  PostalCode          varchar(10)
)
go

INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) 
VALUES (34, 154, N'April 1,2010', N'April 10,2010', N'Sr. .Net Developer', 1, 1, 1, N'P12,000', N'P12,000', N'Design websites for company using Adobe dreamweaver, Flash and other web applications.', N'sample reason for leaving', N'Appsource', N'Information Technology', N'Unit 1401 Robinsons Equit', N'ADB Ave. Corner Poveda Ro', N'Pasig', N'NCR', N'NCR', 177, N'')

INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) 
VALUES (35, 154, N'April 7,2009', N'April 7,2010', N'Lead Software Developer', 1, 2, 1, N'P12,000', N'P12,000', N'Design websites for company using Adobe dreamweaver, Flash and other web applications.', N'sample reason for leaving', N'Corebuilt Technologies', N'Information Technology', N'24/F 88 Corporate Center', N'Valero St.,', N'Makati', N'NCr', N'NCr', 177, N'')

INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) 
VALUES (36, 154, N'April 7,2009', N'', N'Web Developer', 1, 1, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'IDCSI', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', N'', N'NCR', 177, N'')

INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) 
VALUES (38, 155, N'June 12,2008', N'June 12,2009', N'Analyst', 1, 3, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'IDCSI', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', NULL, NULL, 177, NULL)

INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) 
VALUES (40, 156, N'August 1, 2001', N'', N'Quality Controller', 1, 2, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'IDCSI', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', N'', N'', 177, N'')

INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) 
VALUES (41, 165, N'January 12, 1980', N'June 8, 1995', N'Analyst', 1, 2, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'Microsoft', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', NULL, NULL, 177, NULL)

INSERT [dbo].[EmploymentDetails] ([Id], [aId], [Startdate], [Enddate], [Position], [PositionLevelId], [SpecializationId], [PositionId], [StartSalary], [EndSalary], [DescriptionofDuties], [ReasonforLeaving], [CompanyName], [TypeofBusiness], [Address1], [Address2], [City], [Province], [StateorRegion], [CountryId], [PostalCode]) 
VALUES (42, 165, N'August 2,1995', N'', N'Programmer', 1, 3, 1, N'P15,000', N'P15,000', N'sample description', N'sample description', N'Microsoft', N'Information Technology', N'15/F Summit One Tower', N'530 Shaw Blvd.,', N'Mandaluyong', N'', N'', 177, N'')

Answer 1

You should NEVER store dates as varchar(25), use a datetime, smalldatetime, or date data types (date is only in sql server 2008)!!!

for rows where id is 36,40, and 42 the EndDate is not a null, it is an empty string. As a result your ISNULL to attempt to use GETDATE() fails. here is your example query that fails with the fix in place:

select DATEDIFF(YEAR,cast(Startdate as datetime),isnull(cast(NULLIF(Enddate,'') as datetime), GETDATE())) 
from employmentdetails where id=36

Answer 2

You should fix the problem, not the symptom: change your database columns to DATETIME data type and you'll never have to worry about it again.

Answer 3

MS-SQL supports dates from '1753-01-01' to '9999-12-31'. You must be providing a date (or large number in varchar format it is attempting to convert to a date) that is outside this range.