none
How to check Date Format for Update Statement

    Question

  • Hi
    I've a table with 1 Date field

    if the Date format is 31.1.2012 (dd mm yy)
    i want update to 1/31/2012 (mm dd yy)

    if the Date format is 31-1-2012 (dd mm yy)
    i want update to 1/31/2012 (mm dd yy)

    if the Date format is 1/31/2012 (mm dd yy)
    i want to keep it, as it is, without any change.

    i need an update statement for above condition

    please advice

    Friday, July 27, 2012 6:13 PM

Answers

  • Hi,

    update table
    set datecolumn = convert(varchar(10),convert(datetime,replace(replace(datecolumn,'.','/'),'-','/'),103),101)
    where charindex('/',dateColumn) =0


    - Chintak (My Blog)

    • Marked as answer by gvj Tuesday, August 07, 2012 2:14 PM
    Friday, July 27, 2012 6:21 PM
  • ANSI Standard SQL has one and only date display format; ISO-8601  with dashes (yyyy-mm-dd). Any local dialect should be done in the presentation layer of your application. This is the fundamental principle of any tiered architecture and part of correct programming. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, July 30, 2012 11:46 AM

All replies

  • Hi,

    update table
    set datecolumn = convert(varchar(10),convert(datetime,replace(replace(datecolumn,'.','/'),'-','/'),103),101)
    where charindex('/',dateColumn) =0


    - Chintak (My Blog)

    • Marked as answer by gvj Tuesday, August 07, 2012 2:14 PM
    Friday, July 27, 2012 6:21 PM
  • dd-mm-yy ,dd.mm.yyyy 

    select
    convert(varchar,convert(datetime,
    substring('29-10-2007',charindex('-','29-10-2007')+1,2)+'/'+
    left('29-10-2007',charindex('-','29-10-2007')-1)+'/'+
    right('29-10-2007',charindex('-',reverse('29-10-2007'))-1)
    ),101) ,

    convert(varchar,convert(datetime,
    substring('29.10.2007',charindex('.','29.10.2007')+1,2)+'/'+
    left('29.10.2007',charindex('.','29.10.2007')-1)+'/'+
    right('29.10.2007',charindex('.',reverse('29.10.2007'))-1)
    ),101)

    Friday, July 27, 2012 6:30 PM
  • Can you please clarify the structure of your table? If you're using date type, then the format doesn't matter.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Saturday, July 28, 2012 8:11 PM
  • Hi sql !

    MS SQL Server has Date / DateTime data types for storing Dates. It stores dates in ISO Format i.e; "YYYYMMDD" without any dashes. Please always store Dates in specified data types. You can always use CONVERT function to display them in any format you like, but never try to store Dates in VARCHAR or any other data type other than (Date / DateTime).

    Please let me know if this helps. Hopefully i have answered you correctly.

    Thanks, Hasham

    Saturday, July 28, 2012 8:45 PM
  • You can directly try to put the date format in Windows to be

    M/d/yyyy


    Many Thanks & Best Regards, Hua Min

    Sunday, July 29, 2012 2:16 PM
  • ANSI Standard SQL has one and only date display format; ISO-8601  with dashes (yyyy-mm-dd). Any local dialect should be done in the presentation layer of your application. This is the fundamental principle of any tiered architecture and part of correct programming. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, July 30, 2012 11:46 AM
  • Thanks Chintak for your reply

    I got exact answer that I want from your reply

    Tuesday, August 07, 2012 2:18 PM