US Date to UK

[edwardturner]

Hi,

I've got a SQL Table in which I have imported dates in an american format. I am sure I can write a simply update statement to correct them. The select statement below gives me each part of the date (datewef being the field)

select day(datewef), month(datewef), year(datewef)
from TempImport

I would assume I could then do something like this:

update tempimport
set datewef = day(datewef) + '/' + month(datewef) + '/' + year(datewef)

Doing so gives me this error though:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '/' to a column of data type int.

Any ideas welcome?

 

[SQLBill]

One of these will resolve your issue. Depending on how the value is stored (VARCHAR or DATETIME) will determine which you need to run.

SET = (SELECT CONVERT(VARCHAR(10), datewef , 103))

or

SET = (SELECT CONVERT(DATETIME, datewef , 103))

-SQLBill

Posting advice: FAQ481-4875

 

[hooggie]

set datewef = day(datewef) + '/' + month(datewef) + '/' + year(datewef)

The error is because day(datewef) is an integer not a string

so it's treating the + as a mathmatical + not as a string concatenation.

If you wanted your script to work you would need to convert the integer values to strings

e.g.

set datewef = cast(day(datewef) as varchar) + '/' + cast(month(datewef) as varchar) + '/' + cast(year(datewef) as varchar)