473,577 Members | 3,256 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Date Conversion Problem From varchar to Datetime

37 New Member
But the problem is we have dates in Varchar instead Datetime

While Converting Varchar To Datetime All four formats are unable to



select Convert(Datetim e,'18-11-2008 2:35:19 PM',102) -- error

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

select Convert(Datetim e,'11-18-2008 2:35:19 PM',102) ok

select Convert(Datetim e,'18/11/2008 2:35:19 PM',102) Error

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

select Convert(Datetim e,'11/18/2008 2:35:19 PM',102) ok
Nov 19 '08 #1
1 8316
2,878 Recognized Expert Specialist
Standardize your date data first. You can not just use convert. As I said you'll have problems with a valid date string but converted differently.

If I'm to do it, i'll do this:

1. Add a new column to hold the new date string.
2. All string that starts with integer that are greater than 1000 and the next integer (consider the separator) is greater than 12, that's parse your datestring as YYYY/MM/DD and store it on your new column in YYYYMMDD format.
3. If the first integer is greater than 12 and the next is less than 13, parse your string as DD/MM/YYYY and store it as YYYYMMDD
4. Analyze those rows with newcolumn is null. This has to be manually checked, though. Your SQL Server can not figure out for yourself if 01/10/2008 is January 10 or October 1.If you simply use CAST or CONVERT it will use your sql server setting to convert it.
5. Once new column is fully populated, you may then analyze the values, add another smalldatetime/datetime column and populate it with the converted one. Up to you if you will delete those temporary columns. I'm just recommending you create them so that you don't modify the original one.

Good luck!

-- CK
Nov 19 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Sandy Fleming | last post by:
What is the easiest way to force Access to read a date in a specific format? I have an application that imports a delimited text file with a date field in the format "mm/dd/yyyy". I've written a function that converts it to a numeric date (integer).. and it's quite a long, convoluted function (it converts it to a medium date in the...
by: Annie D via AccessMonster.com | last post by:
Problem! I have a series of CSV files that I am bringing in to Access, contained within each of them are 6 date fields, however, they are not being recognized as dates…this is a big problem! the date looks like this: 20050816 23:59:59.000 I wrote a query to split up the numbers and then bring them back together in the right order:
by: Phillip Vong | last post by:
VS2005 in VB.NET I have 2 simple textboxes (Textbox1, Textbox2) and they both have dates. Textbox1=12/31/2006 Textbox2=12/15/2006 I need to convert these two textboxes that are in String format into Date format and I'm not sure how. I tried this and I thought it would work, but I get the
by: pmagee | last post by:
I am trying to convert a date from string format into the SQL date format. This seems to work fine as it prints out as 1999-01-01 - see code below. I then try to insert this as a variable into my table and I get the error: ORA-01858: a non-numeric character found where a digit was expected. Any help would be greatly appreciated try {...
by: shikhachaudhary30 | last post by:
hii all!! i am getting the following error- implict conversion from data type varchar to data type money....USE CONVERT function to run this query... i want to insert a textbox value(textbox7) to database... i m attaching the code also... PLZ HELP!!!!
by: robtyketto | last post by:
Greetings, I'm using MDMZinc to insert/update records into an MSAccess database, many headaches have been caused by dates. Apparently the best method to avoid complication is to record date stamp as a numeric value, which i have done using the flash getTime() function (see below):- var my_date:Date = new Date(); var quiznumberdate =...
by: pchaitanya | last post by:
I have a column named entrydate in my table as datatype varchar as following mm/dd/yyyy . How can I convert the column to datetime format.. I tried the following but i got an error select convert(datetime,entrydate,1) from timeentry I got the following error
by: servantofone | last post by:
I have dates in the format of YYYMMDD that I would like to convert into an actual date. The first digit indicates that the date is after 2000 if it is a 1 and prior to 2000 if it is a 0. So for instance: 1011023 = 10/23/2001. I can convert it using the following statement in an Access query: newDate: IIf(Val(Nz())>0,CDate(Mid(,4,2) & "/" &...
by: maheshwag | last post by:
my code is: string sql="insert into dummy(date)values(@date) sqlcommand cmd=new sqlcommand(sql,con) cmd.parameters.Add("@date", SqlDbtype.datetime).values=maskedtextbox1.text; cmd.ExecuteNonquery();
by: Abhilash Nair | last post by:
I have data with Varchar datatype like 1st January 2011. I want to convert this to date data type, output shud be like 01-01-2011, how can I do this
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.