CODEWITHSUNDEEP
sqlsql-serverdatabasedate
user3537234
user3537234

Reputation: 75

I want to convert a whole column into the date format of yyyymmdd, I do not want the current date

I want to convert a whole column into the date format of yyyymmdd, I do not want the current date, thus I cannot use getdate() command, there is already data in the column, I just need the right command to convert the whole column into yyyymmdd format.

The column I am using is FIELD_034 and the table is Sur_CompassAuto1_1_7_fetch.

I am using SQL Server.

Thank you

Upvotes: 2

Views: 633

Answers (2)

crthompson
crthompson

Reputation: 15875

A common misconception is that a datetime has a format. If you are storing your dates as a datetime, then you can output it in any format.

It sounds as though you might be storing your values as a Varchar. You would be better off converting your varchar dates into a datetime, then you can do whatever you want with them

That said, if you HAVE FIELD_034 as a DateTime, then its as easy as 

SELECT CONVERT(VARCHAR(10), FIELD_034, 112)
from Sur_CompassAuto1_1_7_fetch

If the field is a varchar its similar:

SELECT CONVERT(datetime, FIELD_034, 112)
from Sur_CompassAuto1_1_7_fetch

The difficulty of this one is if you have your values in different or nonstandard formats. Then it would require some clean up to make the query work.

Edit: as @AArnold says, its 112 instead of 111. Date formats are subtle.

Upvotes: 2

A.Anoud
A.Anoud

Reputation: 31

I fully agree with the paqogomez's response, but instead of date style of 111, better use 112. The output for the statement 

SELECT CONVERT(VARCHAR(10), FIELD_034, 111)

will result in yyyy/MM/dd. Where as, 

SELECT CONVERT(VARCHAR(10), FIELD_034, 112)

will return yyyyMMdd, which is what he needed.

Upvotes: 3

Related Questions