Update date feild switch format 1

[alexanderthegreat]

how can I update my field licissuedate from date fields which look like this 20030520 to the short date format 05/20/2003? in my table garages

 

[SQLDenis]

what is the data type of the column right now?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

If your field is datetime or smalldatetime, SQL Server does NOT store dates and times in those fields as recognizable values. You can only set the format by 1) storing the date in a string format (VARCHAR/CHAR) or 2) by setting how it is to be displayed (using CONVERT). Refer to FAQ183-5834 for more information.

-SQLBill

Posting advice: FAQ481-4875

 

[alexanderthegreat]

The data type right now is varchar

 

[SQLDenis]

here is an example

declare @v varchar(20)
select @v ='20030520'

select convert(varchar,convert(datetime,@v),101)

your update query would be like this

update table
set field = convert(varchar,convert(datetime,field),101)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[alexanderthegreat]

update garages
set licissuedate = convert(varchar,convert(datetime,licissuedate),101)

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

 

[SQLDenis]

any invalid dates or nulls?

select * from garages
where isdate(licissuedate) =0

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[alexanderthegreat]

Yes

 

[SQLDenis]

you have to fix those invalid dates or do something like

update garages
set licissuedate = convert(varchar,convert(datetime,licissuedate),101)
where isdate(licissuedate) =1

If you don't want to fix them

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[alexanderthegreat]

Nice!