在SQL Server 2008中将特定日期格式转换为日期时间格式 [英] Converting a particular date format to datetime format in SQL Server 2008

问题描述

我在我的项目中有一个功能，我可以选择日期并相应地从数据库中对记录进行排序。我使用convert（datetime，columnname，111）来转换包含date到datetime格式的列名。这工作得很好......但是日期列不是静态意味着......在我的项目中，用户将导入基于Excel工作表的Excel工作表数据库表默认创建为nvarchar所有列....所以如果excel工作表有日期可以是基于用户输入的Excel工作表的任何格式..例如：



11.07.2013

11/07 / 2013

11-07-2013

2013年3月11日

2013年3月11日

2013-07-11





任何格式......它可以......我需要将其转换为日期时间，然后与文本框中输入的值进行比较这是日期时间格式并相应显示结果...我用过

Hi,

i am having a functionality in my project where i can select date and sort the records from database accordingly. I was using convert(datetime,columnname,111) to convert the column name which contains date to datetime format. This was working fine ... but the date column is not static means... in my project user will import excel sheet based on excel sheet datas database table is created defaultly as nvarchar all columns .... so if the excel sheet has date it can be in any format based on the excel sheet the user enters.. for ex:

11.07.2013
11/07/2013
11-07-2013
11th Mar 2013
11th March 2013
2013-07-11


any format... it can be... i need to convert this to datetime and then compare to values entered in textbox which is in datetime format and display result accordingly... i had used

ColoumnValues = "convert(datetime," + ColoumnNames + ", 111) between '" + ds.Tables[0].Rows[i]["StartDuration"].ToString() + "' and '" + ds.Tables[0].Rows[i]["EndDuration"].ToString() + "'";

这是有效的但不适用于日期时间值以及2013年3月12日...这样的价值......



然后我将代码更改为

this worked but not for datetime values and also 12th March 2013 ... such values...

then i changed code to

ColoumnValues = "convert(datetime," + ColoumnNames + ") between convert(datetime,'" + ds.Tables[0].Rows[i]["StartDuration"].ToString() + "') and convert(datetime,'" + ds.Tables[0].Rows[i]["EndDuration"].ToString() + "')";

这也不适用于2013年3月12日的格式...



当我在查询中使用这些时显示错误

this also did not work for 12th March 2013 format...

it shows error when i use these in queries as

Conversion failed when converting date and/or time from character string.

所以我怎样才能将2013年3月12日转换为日期时间格式... PLZ帮助..



提前感谢你!!

so how can i convert the 12th March 2013 to datetime format ... plz help..

thanking u in advance!!

推荐答案

试试这个

Hi,


Try this

ColoumnValues = "convert(datetime," + replace(ColoumnNames,'th','') + ") between convert(datetime,'" + replace(ds.Tables[0].Rows[i]["StartDuration"].ToString(),'th','') + "') and convert(datetime,'" + replace(ds.Tables[0].Rows[i]["EndDuration"].ToString(),'th','') + "')";

declare @date varchar(20)

set @date= '12-March-2013'

select @date

select CONVERT(datetime,replace(@date,'th',''),111)

这篇关于在SQL Server 2008中将特定日期格式转换为日期时间格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！