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
by Scott Buerkle » Fri, 30 Jan 2004 02:08:00
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
by Trey Walpol » Fri, 30 Jan 2004 02:33:07
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
by Scott Buerkle » Fri, 30 Jan 2004 02:40:15
Thx,
Scott Buerkley
> > 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
by Joe Celk » Fri, 30 Jan 2004 02:59:43
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
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