Update Field 1

[jestrada101]

I have a column with data like this "00-03", which corresponds to "YY-MM" and I would like to update all fields to be like this "MM-YYYY" or "03-2000".

Can someone guide me in the right direction?

This is a text field, but will be changing format to a DATE field.

Thanks
JE

 

[despierto]

To change your format around, you can do this:

 RIGHT('00-03', 2) + '-20' + LEFT('00-03', 2)

replace the two '00-03' with the fieldname.

But if you want a datetime value, you need a day. Assuming you want the 1st of each month, you can do this:

 RIGHT('00-03', 2) + '/01/20' + LEFT('00-03', 2)

And if you want to actually insert it into a datetime field with a default time of midnight:

 CAST(RIGHT('00-03', 2) + '/01/20' + LEFT('00-03', 2) AS datetime)
--or--
 CONVERT(datetime, RIGHT('00-03', 2) + '/01/20' + LEFT('00-03', 2))

Quick note though, this solution is -Y2k compliant. If you have any dates before 2000, they will show up as 2099, 2098, etc. You can handle that by using two update queries with modified WHERE clauses. This is a quick and dirty solution, probably not the best, but I hope it helps.

 

[jestrada101]

Thanks!!! that's exactly what I was looking for!

 

[jestrada101]

how can i put this into an SQL to automatically do the updates?

 

[despierto]

That depends... Which method do you want to use above? Are you putting the data into the same table? Is the data in FieldXYZ the source and destination? Or is the destination field different?

A quick example update statement would be:
UPDATE table SET field=(functions described above)