How to convert datetime format MM-DD-yyyy in sql. [Resolved]

Posted by Karthik2010_Mca under Sql Server on 10/1/2013 | Points: 10 | Views : 5002 | Status : [Member] | Replies : 5
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


Responses

Posted by: Bandi on: 10/2/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Refer this link
http://stackoverflow.com/questions/420623/how-to-convert-date-into-mm-dd-yy-format-in-c-sharp
http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

try this

xCourse.SetAttribute("Dt_Course_Start_Date", this.StudentCourseList[i].CourseStartDate.ToString("MM-dd-yyyy"));


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthik2010_Mca on: 10/1/2013 [Member] Starter | Points: 25

Up
0
Down

Hi,

If I convert the format of Dt_Course_Start_Date "MM-DD-yyyy" it is working.

Ex:

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

Thanks

Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/1/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down

--If @Date variable is VARCHAR then
DECLARE @Date VARCHAR(50) = '26-04-2011'
Select convert(varchar, convert(date, @Date, 103), 110) -- OUTPUT is 04-26-2011


--If @Date is DATETIME type then

--SET DATEFORMAT DMY
GO
DECLARE @Date DATETIME = '26-04-2011'
Select convert(varchar, @Date, 110) -- OUTPUT is 04-26-2011


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthik2010_Mca on: 10/1/2013 [Member] Starter | Points: 25

Up
0
Down

Hi,

We cannot select date format from procedure,
Better we have to change application itself in the same format.

How to change the format in the below mentioned code.

Ex:
xCourse.SetAttribute("Dt_Course_Start_Date", this.StudentCourseList[i].CourseStartDate.ToShortDateString());

Thanks

Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.Saravanan on: 10/1/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

Try this...

DateTime dt = DateTime.Now;
string sdate = dt.ToString("dd/MM/yyyy");
sdate = dt.ToString("MM/dd/yyyy");
sdate = dt.ToString("yyyyMMdd");
sdate = dt.ToString("yyyyMMdd HHmmss");
sdate = dt.ToString("yyyyMMdd hhmmss");


Thanks,
T.Saravanan

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response