Need format in Query ??

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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 AdvisorCommented:
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()
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.