GetDate without time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

GetDate without time

Author

Topic

TimSinnott
Starting Member

48 Posts

Posted - 2002-09-29 : 02:58:15

Hi all,

In reports, I often group records by date created. I want a field in my table called Creation_Date.

If I set its Default to GetDate(), I get a date and time, such as,
3-15-2002 08:15:48

However, I want Creation_Date to have zero time, like this:
3-15-2002 00:00:00

That way I can easily group directly on Creation_Date in my reports so they're simpler and faster.

My question is this: What can I use instead of GetDate() for my Default at record creation?

Thanks in advance.

Tim

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-09-29 : 06:06:27

Keep using getdate() because SQL Server only does datetime. In your queries, you can use CONVERT to only consider the date portion.

For example:

Select field1, sum(field2)
from tbl1
group by CONVERT(datetime, getdate_field, 1)

This will group by individual dates without respect to time.

Please see CAST and CONVERT in BOL for more info.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-09-29 : 12:43:10

If you never want the time then
1) Use smalldatetime datatype and save yourself 4 bytes
2) Use a default that only uses the date part

 
create table t1(
col1 int identity(1,1) PRIMARY KEY,
col2 smalldatetime DEFAULT (convert(char(10),getdate(),112)) NOT NULL)

HTH
Jasper Smith

TimSinnott
Starting Member

48 Posts

Posted - 2002-09-29 : 20:21:37

Thanks, Scott. I can use your suggestion for some of my existing tables.

Jasper, your

quote:
col2 smalldatetime DEFAULT (convert(char(10),getdate(),112)) NOT NULL)

looks like what I want. Yep, that works.

Thanks very much.

SQLTeam is a great site!

Tim

TimSinnott
Starting Member

48 Posts

Posted - 2002-09-29 : 22:07:16

Another question, please:

I've made this user-defined function based on Jasper's formula:

CREATE FUNCTION DateTimeToDateTS(@MyDateTime DateTime)
RETURNS SmallDateTime
AS
BEGIN
RETURN (CONVERT(char(10),@MyDateTime,112))
END

My question is this: How can I use this in Enterprise Manager to set the Default for my column colCreationDate?

In Enterprise Manager, in table design, for the Default property, I've tried:

(DateTimeToDateTS(getdate())

but Enterprise Mgr says this is erroneous.

Any ideas? Thanks.

Tim

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-09-30 : 08:53:43

Generally you would want to use QA to script your table creation statements however if you must use EM then just paste this into the default (dbo.DateTimeToDateTS(getdate()))

HTH
Jasper Smith

TimSinnott
Starting Member

48 Posts

Posted - 2002-09-30 : 12:04:16

Jasper, that worked great. Many thanks -- again. :)

Tim

TimSinnott
Starting Member

48 Posts

Posted - 2002-09-30 : 12:05:58

BTW, Jasper, what is the main advantage of using QA for this rather than EM?

Tim

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-30 : 12:10:15

QA makes you use the SQL statement, instead of relying on EM's techniques. One of the benefits is in the case of altering tables; EM sometimes performs a multi-step process (create new table, move data, drop old table, rename new table with old name, for example) that would be hidden from you, and may cause a huge performance drop or other problems, or could have been accomplished with a simple ALTER TABLE statement. You get to learn SQL a lot faster when the GUI is out of the equation.

Also, once you get proficient with CREATE TABLE and the like, you can create relatively complex tables much faster than using EM. It doesn't take as long as you think either.

TimSinnott
Starting Member

48 Posts

Posted - 2002-09-30 : 13:49:29

Well, I guess I'd better try some of that, then. :)
Thanks, Rob.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources