How to change the Data Type for a Date Field

You can change the format of the field as you query your records.

example:

SELECT FORMAT(as_of_date, 'yyyyMMdd') AS 'FormattedAsOfDate', * FROM FundManagers

or

SELECT FORMAT(as_of_date, 'ddMMyyyy') AS 'FormattedAsOfDate', * FROM FundManagers

you can change the way the field is presented by changing the 2nd parameter

Year -> yyyy = 2023

Month -> MM = 05

Day - > dd = 31

other parameters you can try and play around with:

• dd - this is day of month from 01-31
• dddd - this is the day spelled out
• MM - this is the month number from 01-12
• MMM - month name abbreviated
• MMMM - this is the month spelled out
• yy - this is the year with two digits
• yyyy - this is the year with four digits
• hh - this is the hour from 01-12
• HH - this is the hour from 00-23
• mm - this is the minute from 00-59
• ss - this is the second from 00-59
• tt - this shows either AM or PM
• d - this is day of month from 1-31 (if this is used on its own it will display the entire date)
• us - this shows the date using the US culture which is MM/DD/YYYY

Try using format function, e.g. format(dateColumn, 'yyyyMMdd') assuming dateColumn is the actual date in SQL Server.

Maybe it has sense to keep the reasonable data allocation for as_of_date column. It is possible to define a new computed column that does not need additional space:

alter table [kevin-sqldb].outbound.FundManagers add as_of_date_2 as convert(varchar(8), as_of_date, 112)

Use as_of_date for fast calculations, and as_of_date_2 for formatted output.

For example, the as_of_date field is formatted as 31/05/2023

A column of datatype "date" don't store any format information, it's just a date value.

It's the frontend as presention layer that format the date value into a readable format.

Hi @Carlton Patterson

SQL requires the use of a select statement to display the contents of the query.

If you need to change the date format, just do it in the select statement as Naomi does.

It is not practical to start with the data type of the column.

Best regards,

Percy Tang