SQL Server Performance

Convert string -> date

Discussion in 'T-SQL Performance Tuning for Developers' started by luma, Oct 19, 2005.

  1. luma New Member

    Hi, help me please..
    I need convert string '10.04.2004' to date 10.04.2004 by update. Could you write me correct statement ?
    Thanks, Lubo
  2. Madhivanan Moderator

    What is the DataType of the Column you want to update?


    Failing to plan is Planning to fail
  3. dineshasanka Moderator

  4. FrankKalis Moderator

    Check out CAST or CONVERT. And in case you want some additional checking, have a look at ISDATE() in BOL.

    Frank Kalis
    Microsoft SQL Server MVP
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  5. luma New Member

    I want convert column Datum , varchar(255). After that to write converted value to write to
    column dateKonv ( datetime ). I want upddate this column in procedure

    Value datum = '10.04.2004' ; or if it isn't possible '10.04.04'
    converted value = 10.04.2004 or 10.04.2004
  6. Madhivanan Moderator

    Try this

    Update yourTable set dateKonv =Cast(Datum as DateTime)


    Failing to plan is Planning to fail
  7. luma New Member

    ok, but i don'want format
    2005-04-10 00:00:00.000

    I want only 04.10.2005
  8. Madhivanan Moderator

    Thats not the problem
    You should handle that in your Presentation layer

    Otherwise in your Select Statement use this

    Select Convert(varchar,DateKonv,104) from yourTable


    Failing to plan is Planning to fail
  9. FrankKalis Moderator

  10. Madhivanan Moderator

    Frank, I forget to show your link [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  11. luma New Member

    Problem... When I always make your example result is 2005-04-10 00:00:00.000

    My statement for Update with convert is
    Update Detail set detail.datKOnv=convert(varchar,Datum,104)
  12. luma New Member

    oh my god, it's true . Value in table is 10.04.2004
  13. FrankKalis Moderator

    And to top this, what you see isn't what is stored. <br />Try SELECT CAST('20040410' AS BINARY(<img src='/community/emoticons/emotion-11.gif' alt='8)' />) to see how SQL Server internally stores DATETIME values. Here's another good link on that matter:<a target="_blank" href=http://www.karaszi.com/sqlserver/info_datetime.asp>http://www.karaszi.com/sqlserver/info_datetime.asp</a><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  14. Madhivanan Moderator

    Luma, why do you worry on how date is stored in a table?
    It is your Presentation layer that convert it to the format you want


    Failing to plan is Planning to fail

Share This Page