CREATE TABLE [INTIMINGS] (
[EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INTIME] [datetime] NOT NULL ,
[EFFECTIVE_DATE] [smalldatetime] NULL ,
[END_DATE] [smalldatetime] NULL ,
[ENTERED_BY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_INTIMINGS] PRIMARY KEY CLUSTERED
(
[EMPID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert into timings values(‘1ab’,1899-12-30
09:00.00.00,2003-07-01,00:00:00,’’,jym)
CREATE TABLE [INTIMINGS_HISTORY] (
[EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INTIME] [datetime] NOT NULL ,
[EFFECTIVE_DATE] [smalldatetime] NOT NULL ,
[END_DATE] [smalldatetime] NULL ,
[ENTERED_BY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_INTIMINGS_HISTORY] PRIMARY KEY CLUSTERED
(
[EMPID],
[EFFECTIVE_DATE]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert into [INTIMINGS_HISTORY(‘1CMN’, 1899-12-30 09:00.00.00,’
‘2005-03-01,00:00:00’, ‘2005-02-08,00:00:00’
CREATE TABLE [EMPLOYEE_TIMINGS] (
[ENTRYTIME] [datetime] NOT NULL ,
[EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TIMETYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INOUT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IP_ADDRESS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_EMPLOYEE_TIMINGS] PRIMARY KEY CLUSTERED
(
[ENTRYTIME]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert into EMPLOYEE_TIMINGS(
2003-10-01 00:03:29.000, VVR, S, E, NULL)
2003-10-01 00:03:38.000 SM S E NULL
2003-10-01 00:25:11.000 NA S E NULL
2003-10-01 00:25:18.000 NA S E NULL
2003-10-01 00:25:25.000 AMB S E NULL
2003-10-01 00:38:08.000 KRK S E NULL
2003-10-01 00:51:25.000 KU S E NULL
2003-10-01 02:00:38.000 NZ S E NULL
2003-10-01 05:50:47.000 1FC B S NULL
2003-10-01 06:06:04.000 1IB S E NULL
2003-10-01 06:06:14.000 IH S E NULL
2003-10-01 06:06:21.000 TO S E NULL
2003-10-01 06:07:12.000 EE S S NULL
2003-10-01 06:08:22.000 QX S S NULLwhat is the problem. check quotes in insert
--
Regards
R.D
--Knowledge gets doubled when shared
"raghu veer" wrote:
> CREATE TABLE [INTIMINGS] (
> [EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INTIME] [datetime] NOT NULL ,
> [EFFECTIVE_DATE] [smalldatetime] NULL ,
> [END_DATE] [smalldatetime] NULL ,
> [ENTERED_BY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_INTIMINGS] PRIMARY KEY CLUSTERED
> (
> [EMPID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> Insert into timings values(‘1ab’,1899-12-30
> 09:00.00.00,2003-07-01,00:00:00,’’,jym)
>
>
> CREATE TABLE [INTIMINGS_HISTORY] (
> [EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INTIME] [datetime] NOT NULL ,
> [EFFECTIVE_DATE] [smalldatetime] NOT NULL ,
> [END_DATE] [smalldatetime] NULL ,
> [ENTERED_BY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_INTIMINGS_HISTORY] PRIMARY KEY CLUSTERED
> (
> [EMPID],
> [EFFECTIVE_DATE]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> Insert into [INTIMINGS_HISTORY(‘1CMN’, 1899-12-30 09:00.00.00,’
> ‘2005-03-01,00:00:00’, ‘2005-02-08,00:00:00’
>
>
>
>
>
>
>
>
>
> CREATE TABLE [EMPLOYEE_TIMINGS] (
> [ENTRYTIME] [datetime] NOT NULL ,
> [EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TIMETYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INOUT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [IP_ADDRESS] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_EMPLOYEE_TIMINGS] PRIMARY KEY CLUSTERED
> (
> [ENTRYTIME]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> Insert into EMPLOYEE_TIMINGS(
> 2003-10-01 00:03:29.000, VVR, S, E, NULL)
> 2003-10-01 00:03:38.000 SM S E NULL
> 2003-10-01 00:25:11.000 NA S E NULL
> 2003-10-01 00:25:18.000 NA S E NULL
> 2003-10-01 00:25:25.000 AMB S E NULL
> 2003-10-01 00:38:08.000 KRK S E NULL
> 2003-10-01 00:51:25.000 KU S E NULL
> 2003-10-01 02:00:38.000 NZ S E NULL
> 2003-10-01 05:50:47.000 1FC B S NULL
> 2003-10-01 06:06:04.000 1IB S E NULL
> 2003-10-01 06:06:14.000 IH S E NULL
> 2003-10-01 06:06:21.000 TO S E NULL
> 2003-10-01 06:07:12.000 EE S S NULL
> 2003-10-01 06:08:22.000 QX S S NULL
>
Showing posts with label datetime. Show all posts
Showing posts with label datetime. Show all posts
Wednesday, March 7, 2012
Saturday, February 25, 2012
DBTYPE_DBTIMESTAMP OLE DB question
Folks,
I am trying to use ole db to read a date field created as a DATETIME in C#.
It only seems to work if I set the binding type (wType) to
DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
buffer. The question then is what to do with this raw data. What should I
cast it to, so that I can make use of it? I can't think of date/time types
that are 16 bytes long.
Thank you,
Matthew FlemingHi
The SQL Server data type "Timestamp" has nothing to do with date or time. It
is a binary number that is sequential and is generally used for concurrency
control in applications.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"dermite" <dermite@.discussions.microsoft.com> wrote in message
news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
> Folks,
> I am trying to use ole db to read a date field created as a DATETIME in
> C#.
> It only seems to work if I set the binding type (wType) to
> DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
> buffer. The question then is what to do with this raw data. What should I
> cast it to, so that I can make use of it? I can't think of date/time types
> that are 16 bytes long.
> Thank you,
> Matthew Fleming|||If this is so, then why is the field (which was created as type DATETIME),
readable only with a binding type of DBTYPE_DBTIMESTAMP? I tried
DBTYPE_DATE and it did not work (0 bytes were written to the buffer).
Matthew Fleming
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> The SQL Server data type "Timestamp" has nothing to do with date or time.
It
> is a binary number that is sequential and is generally used for concurrenc
y
> control in applications.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "dermite" <dermite@.discussions.microsoft.com> wrote in message
> news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
>
>|||Mike Epprecht (SQL MVP) (mike@.epprecht.net) writes:
> The SQL Server data type "Timestamp" has nothing to do with date or
> time. It is a binary number that is sequential and is generally used for
> concurrency control in applications.
Yes, but the OLE DB data type DBTYPE_DBTIMESTAMP has everything to do
with date and time. That is in fact how you get back the datetime data type
from SQL Server.
Anyway, the actual question have been sorted out in
microsoft.public.olddb.data. Please to do not post the same question to
different newsgroups independently!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
I am trying to use ole db to read a date field created as a DATETIME in C#.
It only seems to work if I set the binding type (wType) to
DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
buffer. The question then is what to do with this raw data. What should I
cast it to, so that I can make use of it? I can't think of date/time types
that are 16 bytes long.
Thank you,
Matthew FlemingHi
The SQL Server data type "Timestamp" has nothing to do with date or time. It
is a binary number that is sequential and is generally used for concurrency
control in applications.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"dermite" <dermite@.discussions.microsoft.com> wrote in message
news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
> Folks,
> I am trying to use ole db to read a date field created as a DATETIME in
> C#.
> It only seems to work if I set the binding type (wType) to
> DBTYPE_DBTIMESTAMP. When I do this, 16 bytes of data are written to my
> buffer. The question then is what to do with this raw data. What should I
> cast it to, so that I can make use of it? I can't think of date/time types
> that are 16 bytes long.
> Thank you,
> Matthew Fleming|||If this is so, then why is the field (which was created as type DATETIME),
readable only with a binding type of DBTYPE_DBTIMESTAMP? I tried
DBTYPE_DATE and it did not work (0 bytes were written to the buffer).
Matthew Fleming
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> The SQL Server data type "Timestamp" has nothing to do with date or time.
It
> is a binary number that is sequential and is generally used for concurrenc
y
> control in applications.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "dermite" <dermite@.discussions.microsoft.com> wrote in message
> news:614ADA8C-E424-4419-9FBC-B86C93646E80@.microsoft.com...
>
>|||Mike Epprecht (SQL MVP) (mike@.epprecht.net) writes:
> The SQL Server data type "Timestamp" has nothing to do with date or
> time. It is a binary number that is sequential and is generally used for
> concurrency control in applications.
Yes, but the OLE DB data type DBTYPE_DBTIMESTAMP has everything to do
with date and time. That is in fact how you get back the datetime data type
from SQL Server.
Anyway, the actual question have been sorted out in
microsoft.public.olddb.data. Please to do not post the same question to
different newsgroups independently!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)