# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Format String Dates In SQL Server 2000

## Moe1950

I have a column in SQL Server 2000 table that was created as a varchar(10).

It stores a date.     The records that get imported into this table are created externally and the date format can literally be anything from 1/1/2010 to 01/01/2010 to 01/1/2010, to 1/01/2010 ect.

The program that does the import is not mine and I have no control over it source so I can't control the formatting at that level.

I do, however, have control over the SQL Server table that the program dumps data into.    I would like to know if it is possible to run a query (maybe an UPDATE query) that would reformat all the dates in the column to mm/dd/yyyy.

----------


## rmiao

Take look at convert function in books online.

----------


## SDas

Moe1950, are the Month and Day value, at least, in the same order.  If not, your biggest problem is how to determine if 01/02/2010 is 1 Feb 2010 or 2 Jan 2010.  Just be glad there is a four digit year.  Trying to determine what 09/10/11 is without know which are Year, Month or Day values is useless.

----------


## Moe1950

> Take look at convert function in books online.


It is just fine as a varchar.   I don't want to CONVERT it (or CAST it)...I want to format it.


> Moe1950, are the Month and Day value, at least, in the same order.


Yes...it will ALWAYS be month/day/year

----------


## rmiao

It can stay as varchar after conversion, but why varchar if you know value always has 10 characters?

----------


## Moe1950

I admit it is unusual for a date to be something other than a datetime field type.

However, there are several departments in the company that run programs/queries against the data in these tables, and those programs are expecting a varchar field.   If I actually converted the field type to datetime I would be messing up a whole bunch of people.

(I know because when I first took maintenance of this database over I did change the field type, and you do not want to know about the 47 kinds of doo-doo I got into for that  :Roll Eyes (Sarcastic):  )

I'm pretty familiar with CONVERT...I have used it times beyond count in stored procedures and ad hoc queries.    I don't see how it is going to address what I'm trying to do in this case, however.

----------


## rmiao

select substring(convert(varchar(10), CONVERT(datetime, '1/1/2010'), 101), 1, 10);

----------

