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

Answer 1

Hi,

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

---

- Chintak (My Blog)

Answer 2

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)

Answer 3

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

Answer 4

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

Answer 5

You can directly try to put the date format in Windows to be

M/d/yyyy

---

Many Thanks & Best Regards, Hua Min

Answer 6

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

Answer 7

Thanks Chintak for your reply

I got exact answer that I want from your reply