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
 Strange datetime format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ajt-systems
Starting Member

United Kingdom
3 Posts

Posted - 11/30/2011 :  02:44:05  Show Profile  Reply with Quote
Hi there,

I am importing a CSV into my database. There is a colum in the CSV file with what looks to be a date\time stamp, it looks like this

24/11/2011 22:03:19

I am trying to impoprt this data into a SQL table with a column that has a Varchar format which works but when i run a query the dates and times are not in order.

When I convert the table column to datetime SQL says the datetime format above is not recognised as datetime.

Can anyone help or say a way to import succesfully into a date time column?

Thanks

James

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/30/2011 :  03:21:55  Show Profile  Reply with Quote
why are you trying to store this in a varchar field? why not make the target field a datetime and then pass it in iso format (yyyymmdd hh:mm:ss) to avoid ambiguity

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

Go to Top of Page

jassi.singh
Posting Yak Master

122 Posts

Posted - 11/30/2011 :  03:38:59  Show Profile  Visit jassi.singh's Homepage  Reply with Quote
check this link

http://www.equalis.com/forums/posts.asp?topic=266276
Go to Top of Page

ajt-systems
Starting Member

United Kingdom
3 Posts

Posted - 11/30/2011 :  03:56:27  Show Profile  Reply with Quote
Hi Visakh16,

Thanks for the comment. I know, it sounds very straight forward but I im importing using Powershell.

When trying to import in Powershell the default format is Varchar and if importing into a datetime column in SQL I get an error in Powershell saying cannot import Varchar into datetime column.

My plan was to stick with the import method and then run a SQL query and maybe Cast or Convert the string to a datetime format.,..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/30/2011 :  10:47:33  Show Profile  Reply with Quote
quote:
Originally posted by ajt-systems

Hi Visakh16,

Thanks for the comment. I know, it sounds very straight forward but I im importing using Powershell.

When trying to import in Powershell the default format is Varchar and if importing into a datetime column in SQL I get an error in Powershell saying cannot import Varchar into datetime column.

My plan was to stick with the import method and then run a SQL query and maybe Cast or Convert the string to a datetime format.,..


ok. you can cast or convert as long as you're sure that format in which date values come from varchar field is consistent

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

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.06 seconds. Powered By: Snitz Forums 2000