Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update varchar to date

Author  Topic 

montecarlo2079
Starting Member

20 Posts

Posted - 2013-01-11 : 12:08:37
IS there a way to update every value in a column that is a varchar to date format?

I import a very large txt file and it only lets me import it as varchar.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 12:21:50
make sure you read this

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

montecarlo2079
Starting Member

20 Posts

Posted - 2013-01-11 : 12:26:16
I read that. I Actually pull data from business objects. I save it as a text file and I use sql server to manipulate the data as its faster than using BO.

SO your saying I can change the data type that I return in a specific format in my BO query and that way when I Import it as a text file into sql server it will work?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 12:34:17
if format is inconsistent it will cause all sorts of problems while trying to convert to date. If format is all same, use CONVERT() function

http://msdn.microsoft.com/en-us/library/ms187928.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

montecarlo2079
Starting Member

20 Posts

Posted - 2013-01-11 : 12:38:57
I tried using your format.

When I import the txt file, I changed the format to datetime and I received an error.

I change it back to Varchar and it imported fine.

now when i use my select statement and use a condition regarding dates it is working.

Thanks!
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-11 : 13:20:53
1. What is the source format for the date string and can you change the format?
2. How are you importing from a text file to SQL Server?
Go to Top of Page

montecarlo2079
Starting Member

20 Posts

Posted - 2013-01-11 : 14:14:47
1. the source format is m/d/yyyy
2. I am importing it as a flat file source which creates its own table.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-11 : 14:39:10
quote:
Originally posted by montecarlo2079

1. the source format is m/d/yyyy
2. I am importing it as a flat file source which creates its own table.

A flat file source...? Are you using SSIS or BCP or?
Go to Top of Page
   

- Advertisement -