Results 1 to 7 of 7

Thread: Format String Dates In SQL Server 2000

Hybrid View

  1. #1
    Join Date
    Dec 2009
    Posts
    79

    Format String Dates In SQL Server 2000

    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.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Take look at convert function in books online.

  3. #3
    Join Date
    Apr 2009
    Posts
    86
    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.

  4. #4
    Join Date
    Dec 2009
    Posts
    79
    Quote Originally Posted by rmiao View Post
    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.
    Quote Originally Posted by SDas View Post
    Moe1950, are the Month and Day value, at least, in the same order.
    Yes...it will ALWAYS be month/day/year

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    It can stay as varchar after conversion, but why varchar if you know value always has 10 characters?

  6. #6
    Join Date
    Dec 2009
    Posts
    79
    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 )

    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.

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    select substring(convert(varchar(10), CONVERT(datetime, '1/1/2010'), 101), 1, 10);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •