Set default time in SQL Server

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 2005 Forums

Transact-SQL (2005)

Set default time in SQL Server

Author

Topic

ndindi22
Starting Member

14 Posts

Posted - 2007-12-11 : 01:29:44

Hi guys

I'm writing a Stored Procedure, on SQL Server 2005. I need to set a default time for the clock system, The clock in/out system works like this: When you clock in = 0, Clock Out = 1. So other employees they forget to clock out, we want to set a default time that when the employee didn't clock out then clock out time is 4:00PM.

I've been struggling I don't know how to tackle it.
Please help.

Ndi

ndindi22

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-11 : 02:07:24

In the column properties 'Default Value or Binding' specify 4:00:00 PM
of your column (if the datatype of the column is datetime).
But the problem is the default value will be set to
01/01/1900 4:00:00 PM & not just to 4:00:00 PM as datatype datetime does not support just the Time part.

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-11 : 03:06:49

ndindi22,

You better post your table DDL, sample data and the required result

KH
[spoiler]Time is always against us[/spoiler]

ndindi22
Starting Member

14 Posts

Posted - 2007-12-11 : 03:52:52

Hi khtan

You loosing me now, DDL? sample data and required result?

ndindi22

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-11 : 03:55:44

DDL = Data Definition Language aka the create table statement . . .

KH
[spoiler]Time is always against us[/spoiler]

ndindi22
Starting Member

14 Posts

Posted - 2007-12-11 : 04:15:43

Here is Table structure below:

CREATE TABLE [dbo].[TAData](
[TAID] [int] NOT NULL,
[DateTime] [datetime] NULL,
[DateTimeEx] [float] NULL,
[DeviceID] [int] NULL,
[DeviceName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StaffID] [int] NULL,
[UserID] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Title] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FName] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LName] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Gender] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Number] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompanyID] [int] NULL,
[CompanyName] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ActionType] [int] NULL,
[TACode] [int] NULL,
[Exported] [bit] NOT NULL
) ON [PRIMARY]

(and I have created a view, below to separate date and time, now I'm using the View)

SELECT
TAID, DateTime, LEFT(DateTime, 11) AS Date, RIGHT(DateTime, 8) AS Time, DateTimeEx, DeviceID, DeviceName, StaffID, UserID, Title, FName, LName,
Gender, Number, CompanyID, CompanyName, ActionType, TACode, Exported
FROM dbo.TAData

The system needs to set a default time if the epmloyee didn't clock out in other words if TACode= '0'

ndindi22

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 04:20:03

If he didn't clock out, will there still be a record for him, just in case?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources