sql server mseq: date format -- sql server mseq

Re: date format

Tiffany
6/21/2006 6:41:02 PM

Hi Arnie,

Thanks for your suggestions, however the csv file is extremely large and is
impossible to load the entire database in excel application. Hence, I have to
convert it in sql. Any solution available?

[quoted text, click to view]

Re: date format

Arnie Rowland
6/21/2006 10:15:41 PM

You could use repeated REPLACE() function calls;

REPLACE( ColumnToChange, '6-Mar', '03/01/2006' )

OR

UPDATE MyTable
SET ColumnToChange =3D=20
CASE ColumnToChange=20
WHEN '6-Mar' THEN '03/01/2006'
WHEN '6-Apr' THEN '04/01/2006'
WHEN '6-May' THEN '05/01/2006'
WHEN 'etc.' THEN 'Also etc.'
END

That will update the table in the most efficient manner -in one command. =
You can more easily see the entire set of changes in one place this way.

After you update the data, are you planning to change the column to a =
datetime datatype?

--=20
Arnie Rowland, YACE*=20
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

[quoted text, click to view]

Re: date format

Arnie Rowland
6/21/2006 10:45:35 PM

Tiffany,

There are multiple ways to do this conversion, typically using various =
date functions. If the range of possibilites is somewhat limited (a few =
dozen), some think that the Update/CASE statement that I sent you may be =
the easiest for to understand and work with.=20

While the option below may seem a bit more refined, it requires with =
certainity that certain patterns are consistant. The Update/CASE just =
doesn't change the exception rows, leaving them for you to find and =
replair.

Another option is:

UPDATE MyTable
SET ColumnToUpdate =3D REPLACE(ColumnToUpdate, '6-', '2006 ') + ' 01'
WHERE ColumnToUpdate LIKE '6-%'

And a similar statement for 2005, 2004, etc. (Note the 'blanks' in the =
line above.)

--=20
Arnie Rowland, YACE*=20
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

[quoted text, click to view]

Re: date format

Hugo Kornelis
6/25/2006 12:00:00 AM

[quoted text, click to view]

Hi Tiffany,

If the format is always one numeric character, one dash, three letters
for the month (using stnadard English abbreviations), then try this
(changing the variable to your column name, of course)

DECLARE @TheDate char(5)
SET @TheDate = '6-Mar'
SELECT CONVERT(datetime, '1 ' + RIGHT(@TheDate, 3)
+ ' 200' + LEFT(@TheDate, 1))

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.