Date Conversion from MMDDYY string

• Carolyn Stern

  Right there with Babe

  Points: 767

  More actions

  January 11, 2007 at 4:57 pm

  #171777

  My goal was to import a nightly text file with a few fields into a SQL Table with the same fields.

  Only problem is that the text file had four dates of MMDDYY and I just couldn't figure how to convert them or force SQL to move them into the table.  They were set to Date Timestamp and I modified the table so they were a short date and finally gave up and decided I didn't need those darned dates anyway so I removed them from the table.

  I know I could have rewritten the text file dump so that these fields were something that SQL would like (whatever that might be), but I really thought either the Data Conversion or Derived Columns should have done this successfully.  I never saw using the data conversion anything to tell "it" that the string with a date was in MMDDYY format.  (Like Access).  And the Derived Column just never got me anywhere.

  There must be an easy way to have done this as surely thousands of these kinds of conversions are occurring every day out there.

  Now that I have tossed in the towel and moved on, I am just wondering if I missed something?  Should I have been able to do this using SSIS without some fancy code somewhere?

• shashi kant

  SSCommitted

  Points: 1659

  More actions

  January 11, 2007 at 9:37 pm

  #682807

  Hi Carolyn stren

  You just send us your simple code

  there r following date converstion function such as :-

  Select convert(varchar(20),getdate(),100)

  Jan 12 2007  9:59AM

  Select convert(varchar(20),getdate(),101)

  01/12/2007

  Select convert(varchar(20),getdate(),102)

  2007.01.12

  Select convert(varchar(20),getdate(),103)

  12/01/2007

  Select convert(varchar(20),getdate(),104)

  12.01.2007

  Select convert(varchar(20),getdate(),105)

  12-01-2007

  Select convert(varchar(20),getdate(),106)

  12 Jan 2007

  Select convert(varchar(20),getdate(),107)

  Jan 12, 2007

  ................................etc..

  as you chose your string date method and try ....

   

• MohammedU

  SSCoach

  Points: 19644

  More actions

  January 11, 2007 at 10:43 pm

  #682812

  Check BOL topic "CAST and CONVERT (Transact-SQL) " for all conversion info...

  MohammedU
  Microsoft SQL Server MVP