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
 Strange datetime format

Author  Topic 

ajt-systems
Starting Member

3 Posts

Posted - 2011-11-30 : 02:44:05
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

52326 Posts

Posted - 2011-11-30 : 03:21:55
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 - 2011-11-30 : 03:38:59
check this link

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

ajt-systems
Starting Member

3 Posts

Posted - 2011-11-30 : 03:56:27
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

52326 Posts

Posted - 2011-11-30 : 10:47:33
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
   

- Advertisement -