| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-18 : 11:58:32
|
| Sherry writes "I create a table in SQL Server 2000 DB. Two of the columns are "LoginDate" and "LoginTime". I need that SQL Server can insert appropriate time and data into these 2 fields automatically when the rest of the field data are inserted manually into this table. I don't know if this is timestamp function. but there is only one timestamp allowed to be used in one table in SQL Server. right? Would you please let me know how I could solve this problem? Thanks a lotSherry" |
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-07-18 : 12:09:31
|
| Set a default for the fields of GETDATE() and use CONVERT to get the format you want (i.e. time, date, day of week etc..) |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-07-18 : 15:06:04
|
| TIMESTAMP datatype (aka ROWVERSION) has nothing to do with date or time despite its name. Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. Timestamp is used typically as a mechanism for version-stamping table rows.As suggested previously GETDATE() is the function that returns the current date and time. Your columns should be of the datetime datatypeTo get just the date select convert(char(10),getdate(),112)To get just the timeselect convert(char(8),getdate(),108)HTHJasper Smith |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-07-18 : 16:03:50
|
| If I wanted a "changed date" field for a record, would I put a Update trigger on the table and set changedate to getdate()?That's the way i currently do it, but having all those triggers is not fun to maintain.Michael<Yoda>Use the Search page you must. Find the answer you will. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-18 : 16:16:19
|
quote:
If I wanted a "changed date" field for a record, would I put a Update trigger on the table and set changedate to getdate()?That's the way i currently do it, but having all those triggers is not fun to maintain.Michael<Yoda>Use the Search page you must. Find the answer you will.
Only way to do it, it is. Sorry I am, not liking it you are.<O> |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-07-18 : 17:26:21
|
Unless all your table access is via stored procedures (and if not why not ) in which case its simply a default in the update stored procedure.HTHJasper Smith |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-07-18 : 17:46:17
|
| I've thought abotu the update stored proc way, but I want to be able to catch stuff that folks do outside of stored procs. Our programs use stored procs, but our humans don't.Thanks for the insights!Page47, Right you are yess.....Michael<Yoda>Use the Search page you must. Find the answer you will. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-07-18 : 18:41:50
|
| Well we're in the fortunate position of not allowing anyone access to any databases except via stored procedures. We don't allow direct table access to anyone except for us DBA's (although the developers have read access)HTHJasper Smith |
|
|
|
Thorkyl
Starting Member
3 Posts |
Posted - 2002-08-01 : 10:44:05
|
| same line...User may not enter a value in a reqiured field.I can check the inserted table for the value.If its blank how would I populate that column from within the trigger? |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-08-01 : 14:22:35
|
| CREATE TRIGGER RequiredFieldON MyTableFOR INSERTASDECLARE @varRequired nvarchar(50)SELECT @varRequired = MyRequiredField FROM INSERTEDIF @varRequired = ''BEGINUPDATEMyTableSET MyRequiredField = 'MyDefaultValue'WHEREMyTable.MyTableID = @@IDENTITYEND |
|
|
|
Thorkyl
Starting Member
3 Posts |
Posted - 2002-08-01 : 16:12:14
|
| Thanks that got it |
|
|
|
|
How does SQL Server auto insert time and data into table?