Hi,
How to convert datetime format MM-DD-yyyy in sql.
I got this error "Conversion failed when converting date and/or time from character string." during this below mentioned query execution.
The date is getting from (@sCoursesXMl)xml format
The mentioned line is getting from code behind.
//xCourse.SetAttribute("Dt_Course_Start_Date",this.StudentCourseList[i].CourseStartDate.ToShortDateString());
Ex:
DECLARE @sCoursesXMl xml
DECLARE @sCreatedBy varchar(20)
DECLARE @dtCreatedDate Datetime
SET @sCoursesXMl = '<Student I_Student_Detail_ID="132228"><Course I_Centre_ID="767" I_Batch_ID="5032" I_Course_ID="2511" I_Delivery_Pattern_ID="98" I_Fee_Plan_ID="36230" C_Is_LumpSum="Y" Dt_Course_Start_Date="27-05-2013 00:00:00" /></Student>'
SET @sCreatedBy = 'sa'
SET @dtCreatedDate = '10/01/2013 5:42:25 PM'
SET NOCOUNT ON
DECLARE @iUserCount int
DECLARE @iEnquiryRegnID int
DECLARE @iCenterId int
DECLARE @iStudentDetailId int
DECLARE @iUserId int
DECLARE @iCourseId int
DECLARE @iDeliveryPatternId int
DECLARE @iFeePlanId int
DECLARE @iCourseCenterDeliveryId int
--DECLARE @iTimeSlotId int
DECLARE @cIsLumpSum char(18)
DECLARE @sLoginID varchar(50)
DECLARE @AdjPosition SMALLINT, @AdjCount SMALLINT
DECLARE @CourseDetailXML xml
DECLARE @EnquiryXML xml
DECLARE @dtCourseStartDate datetime
DECLARE @dtCourseEndDate datetime
DECLARE @iCourseDurationDays int
DECLARE @iBatchId INT
BEGIN TRANSACTION
SET @AdjPosition = 1
SET @EnquiryXML= @sCoursesXMl.query('/Student[position()=sql:variable("@AdjPosition")]')
SELECT @iStudentDetailId = T.b.value('@I_Student_Detail_ID','int')
FROM @EnquiryXML.nodes('/Student') T(b)
SELECT @iUserCount = MAX(I_Student_Detail_ID) FROM T_Student_Detail
SET @sLoginID = 'Student'+CAST(@iUserCount AS VARCHAR)
SET @AdjCount = @sCoursesXMl.value('count((Student/Course))','int')
WHILE(@AdjPosition<=@AdjCount)
BEGIN
SET @CourseDetailXML = @sCoursesXMl.query('/Student/Course[position()=sql:variable("@AdjPosition")]')
SELECT @iCenterId = T.a.value('@I_Centre_ID','int'),
@iBatchId = t.a.value('@I_Batch_ID','INT'),
@iCourseId= T.a.value('@I_Course_ID','int'),
@iDeliveryPatternId= T.a.value('@I_Delivery_Pattern_ID','int'),
@iFeePlanId = T.a.value('@I_Fee_Plan_ID','int'),
--@iTimeSlotId = T.a.value('@I_Time_Slot_ID','int'),
@cIsLumpSum = T.a.value('@C_Is_LumpSum','char(18)'),
--CONVERT (char (10), dateadd (day, 1, '@DAY_ID'), 101)
@dtCourseStartDate = T.a.value('@Dt_Course_Start_Date','datetime')
FROM @CourseDetailXML.nodes('/Course') T(a)
SELECT @iCourseCenterDeliveryId= I_Course_Center_Delivery_ID
FROM T_Course_Center_Delivery_FeePlan
WHERE I_Course_Fee_Plan_ID = @iFeePlanId
AND I_Status<>0
AND I_Course_Center_ID IN
(SELECT I_Course_Center_ID FROM T_Course_Center_Detail
WHERE I_Centre_ID=@iCenterId
AND I_Course_ID=@iCourseId
AND I_Status<>0
AND @dtCreatedDate >= ISNULL(Dt_Valid_From,@dtCreatedDate)
AND @dtCreatedDate <= ISNULL(Dt_Valid_To,@dtCreatedDate)
)
AND I_Course_Delivery_ID IN
(
SELECT I_Course_Delivery_ID FROM T_Course_Delivery_Map
WHERE I_Course_ID = @iCourseId
AND I_Delivery_Pattern_ID = @iDeliveryPatternId
AND I_Status<>0
Karthik