Could someone help me with the most efficient way to update a date field.I have a field with Date and Time in it but I want to update it with only the Date and strip off the time. DATETIME / SMALLDATETIME columns always contain both date and time.
You can format the value without the time for display and printing purposes.A datetime value is always stored in a binary form.You can do some formattinig when you select the data.For instance to get the date portion only: SELECT convert(char(8), datetimecol, 112) FROM tbl -- Erland Sommarskog, SQL Server MVP, [email protected] Online for SQL Server SP3 at question was about updating.As long as your data contains only the date portion, your queries will work as expected.However, if a time portion is involved, it gets complicated.
Before talking about the complications of querying for dates, we will look at the most important built-in functions for working with dates. This is because the query is looking only for dates with no time portion.
Tip: If you want to keep your queries simple and easy to maintain, do not allow time components in your dates!
Usually your application program is the best place to format the date.
-- David Portas SQL Server MVP -- DATETIME / SMALLDATETIME columns always contain both date and time.
-- David Portas SQL Server MVP -- Your question was about updating.
And you cannot change the format of a datetime column in SQL Server.