Convert Text to Date DataType

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  August 21, 2012 at 5:38 am

  #1528062

  Sergiy (8/20/2012)

  ---

  CELKO (8/19/2012)

  ---

  2. CONVERT() is an expensive string formatting function, with all those old Sybase options; substring and concatenation are cheap, simple and fast.

  ....

  Turns out, SUBSTRING with concatenations is not that fast. It's the slowest option, after all.

  And CONVERT is faster than CAST. Only marginally, but still.

  It may be because of that:

  set showplan_all on

  go

  select CAST('20120101' as DATE)

  from dbo.MyTable

  Check what is the physical operation for the CAST really is in MS SQL Server...

  Mr.Celko, you will be surprised, it is:

  |--Compute Scalar(DEFINE:([Expr1003]=CONVERT(date,[@1],0)))

  Oops...

  :w00t:

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]