473,322 Members | 1,421 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Date Conversion Problem From varchar to Datetime

Manikgisl
But the problem is we have dates in Varchar instead Datetime

While Converting Varchar To Datetime All four formats are unable to

Convert

ie

select Convert(Datetime,'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(Datetime,'11-18-2008 2:35:19 PM',102) ok


select Convert(Datetime,'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(Datetime,'11/18/2008 2:35:19 PM',102) ok
Nov 19 '08 #1
1 8296
ck9663
2,878 Expert 2GB
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

4
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...
4
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!...
4
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...
5
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...
1
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...
0
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...
1
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 ...
5
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...
4
maheshwag
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;...
1
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
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.