Current date and time as the Default Value for a DateTime Field

Current date and time as the Default Value for a DateTime Field

Post by Scott Buerkle » Fri, 30 Jan 2004 02:08:00



Hello all.

Is there a way to have the system date and time entered into a datetime
field when the record is entered into the table?

I see the Default Value column, but what would I enter there to get this to
work?

Thx,
Scott Buerkley

 
 
 

Current date and time as the Default Value for a DateTime Field

Post by Trey Walpol » Fri, 30 Jan 2004 02:33:07


either GETDATE() or CURRENT_TIMESTAMP


Quote:> Hello all.

> Is there a way to have the system date and time entered into a datetime
> field when the record is entered into the table?

> I see the Default Value column, but what would I enter there to get this
to
> work?

> Thx,
> Scott Buerkley


 
 
 

Current date and time as the Default Value for a DateTime Field

Post by Scott Buerkle » Fri, 30 Jan 2004 02:40:15


That works great!!

Thx,
Scott Buerkley


> either GETDATE() or CURRENT_TIMESTAMP



> > Hello all.

> > Is there a way to have the system date and time entered into a datetime
> > field when the record is entered into the table?

> > I see the Default Value column, but what would I enter there to get this
> to
> > work?

> > Thx,
> > Scott Buerkley

 
 
 

Current date and time as the Default Value for a DateTime Field

Post by Joe Celk » Fri, 30 Jan 2004 02:59:43


CREATE TABLE Foobar
(  ..
 foo_date DATETIME DEFAULT CURRENT_TIMESTAMP ...
 .. );

Do not use getdate(); this is the old proprietary syntax and will not
port.  It is also hard for people who speak standard SQL to understand
-- I had one guy who read it as a request for user input via a procedure
call.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Comparing datetime field (containing date, time values) to a date only string

Hi all

I'm wondering if anybody can help me with comparing a datetime field (containing date, time values in the form mm/dd/yy 0:00:00.000) to a datetime variable containing only a date value.

My SQL statement below ideally should return the following 1 row:
count   catid   category        state
1       602     Performance     British Columbia        

        startdate               enddate
        2001-11-06 18:00:00.000 2001-11-07 00:00:00.000

however, it currently returns none (without erroring).

select count(*) as count, events.catid, category, state, otherstate, startdate, enddate from events join
event_categories on events.catid=event_categories.catid where events.catid between 600 and 699 and
(state='British Columbia' or otherstate='British Columbia') and convert(datetime, startdate, 101)
<=convert(datetime, '11/6/2001' , 101) and convert(datetime, enddate, 101)>=convert(datetime, '11/6/2001' , 101)
 group by events.catid, category, state, otherstate, startdate, enddate

When I modify the above SQL statement (shown below) by adding '10pm' after the '11/6/2001' where I compare the startdate value to 11/6/2001, I do receive the row.

select count(*) as count, events.catid, category, state, otherstate, startdate, enddate from events join
event_categories on events.catid=event_categories.catid where events.catid between 600 and 699 and
(state='British Columbia' or otherstate='British Columbia') and convert(datetime, startdate, 101)
<=convert(datetime, '11/6/2001 10pm' , 101) and convert(datetime, enddate, 101)>=convert(datetime, '11/6/2001' , 101)
 group by events.catid, category, state, otherstate, startdate, enddate

I'm also confused as to why the convert functions in the above SQL statement doesn't truncate the time portion of the startdate value, changing the current value "2001-11-06 18:00:00.000" to "11/6/2001".  I tried using convert(char, startdate, 101) and that does truncate off the time from the date, however, the >= and <= comparison operators fail.

Any feedback would be much appreciated!

Darren

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. Comment On OpenLink and/vs OpenPath ODBC

3. Query datetime-field by date when field contains date + time

4. how to get a msgid from enqueue in Visual Basic (using oo4o)

5. CREATE TABLE: Now() as default value for a date/time field

6. Linux - IIF2000

7. Stored Procedure and default datetime as current time

8. Reading Excel Files thru ODBC

9. Concatenating datetime field and int field to make a date time

10. Treating datetime fields as separate date and time fields

11. default value current date

12. Current date as default-value

13. Default value for datetime field