Need format in Query ??

Overthere used Ask the Experts™
I have a problem which confuses me.

I added a column, ChangeDate, datatype of datetime and set the default value/Binding to getdate().
I populated the ChangeDate column, using a query by extracting  from another column, RecUpdate, datatype nvarchar(50).
RecUpdate looks like this: UPDATED-4/6/2011 10:11:59 AM. This a string created with coding.
Notice the format of the date is M/D/YYYY
The query executed fine, but it formatted the dates this way: 2011-04-06 10:11:59.000. It formatted it as YYYY-MM-DD.
Why is this? Do I need to add a format to my query? The query is listed below:
Update BuildingMaster set ChangeDate = (cast(SUBSTRING(RecUpdate 9, 30) AS datetime))

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
A datetime field is a value that is not based on display.  You can display the data by modifying a field in a query by using a varchar (string) value.

The below example shows data in the "mm/dd/yyyy" format:

SELECT CONVERT(varchar(50), ChangeDate, 101) AS FormattedDate
FROM BuildingMaster
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Data types that are date/datetime are stored internally as relative integers from a "base" date/time.  Thus, day 0 is 1900-01-01, day 1 is 1900-01-02, etc..

2011-04-06 is day# 41947, and it physically stored that way in the table.  SQL puts it into the format you see:
2011-04-06 10:11:59.000
by default basically to make it easier for humans to read and use.

If you don't like that format, you can use a CONVERT() function to tell SQL to use a different output format.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
A common misconception is that SQL Server stores these datatypes in some particular readable format. That is not the case. SQL Server stores these values in an internal format (for instance two integers for datetime and smalldatetime).
The ultimate guide to the datetime datatypes

>>"The query executed fine, but it formatted the dates this way: 2011-04-06 10:11:59.000"
NO: it did not format the dates at all
The query executed fine and populated your datetime column with datetime values.

You then looked at the results, and when you did that the default display style for datetime presented the datetime values like this one: 2011-04-06 10:11:59.000

You can alter the default display style, and/or you can alter the format of the column in a query. But please understand there is a strong difference between what we humans "see" and what the dbms is storing.

For display of date/time information you can use CONVERT() with a style number, or in recent sql server versions use FORMAT() . See SQL Server Date Styles (formats) using CONVERT()

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial