SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update varchar to date		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

montecarlo2079
Starting Member

20 Posts
Posted - 01/11/2013 :  12:08:37  Show Profile  Reply with Quote
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

India
47173 Posts
Posted - 01/11/2013 :  12:21:50  Show Profile  Reply with Quote
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 - 01/11/2013 :  12:26:16  Show Profile  Reply with Quote
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

India
47173 Posts
Posted - 01/11/2013 :  12:34:17  Show Profile  Reply with Quote
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/
Edited by - visakh16 on 01/11/2013 12:35:06
Go to Top of Page

montecarlo2079
Starting Member

20 Posts
Posted - 01/11/2013 :  12:38:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3833 Posts
Posted - 01/11/2013 :  13:20:53  Show Profile  Reply with Quote
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 - 01/11/2013 :  14:14:47  Show Profile  Reply with Quote
1. the source format is m/d/yyyy
2. I am importing it as a flat file source which creates its own table.
Edited by - montecarlo2079 on 01/11/2013 14:15:14
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3833 Posts
Posted - 01/11/2013 :  14:39:10  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000