Save only the date in a datetime field

Hello. A question please. Can I save only the date in a datetime field ? I don't want the hours.

Do you are using .NET? Use System.DateTime.Today instead of System.DateTime.Now.

Do you want a TSQL form to obtain only the date?

This is more complex:

Create a Function such as this:

create function DateOnly(@.date as datetime )

returns datetime

as

begin

return cast(CONVERT(CHAR(8),@.date, 112) as datetime)

end

Then use it in the following form:

update mytable set mydate = dbo.DateOnly(getdate()) where id = @.id

or

select dbo.DateOnly(getdate()) as onlythecurrentdate

Regards,

|||

My recommendation is to save the [Time] value.

You can ignore it for your current reporting needs, and if you even in the future decide to use the [Time] value, it will be in the database. (Business needs do often change. Adding [Time] values to existing data will, most likely, be impossible.

|||

Not really. Using any of the intrinsic date datatypes, you will always have the time portion being stored. But, the default time is midnight, so it is common practice to just store date information by making sure to only pass in the date portion, and you can basically ignore the time.

If you don't trust users and the other programmers to get it right (and who does :) then you could put an instead of trigger on tables with dates to make sure (which I would actually do, if you have an automated way to build code, like data modeling tools, or simply using system metadata)

|||

As previously stated the Date types in SQL will save the hours, etc. But, you can can remove them from the date so they are all teh same (as is 00:00:00.000)

One method was shown above to CONVERT the date. Another is to do date math:

SELECT DATEADD(D, 0, DATEDIFF(D, 0,GETDATE()))

Cheers

|||

As mentioned in this thread, there is no date only data type in SQL Server. So even if you don't specify the time part, SQL Server will default the value to 12:00 AM for any smalldatetime/datetime value. You can use convert or date arithmetic etc but there is not that much point since you will have to remember to do the same for any value that is compared against the column anyway. So just save the value as is and ignore the time part in the WHERE clause. This is more efficient to do and there are easy ways to write the conditions using lower/upper bound checks.

Another option that is commonly used in data warehouses is to have a date dimension table or calendar table in your database that has an integer key. You can then use this key in your tables. This provides lot of flexibility and storage benefits too since you can represent date values more compact. This does require additional join to date table but the benefits far outweigh the advantages in data warehouse environments. This is not suitable for OLTP environments but it is not uncommon to use such techniques there too. It depends on your database design and application needs.