troubleshooting Question

DateTime comparison - once more :(

Avatar of trg_dk
trg_dkFlag for Denmark asked on
Microsoft SQL Server
7 Comments1 Solution558 ViewsLast Modified:
Hi

In a MS SQL Server 2005, I need to query for all records from date1 until date 2 - this fails "sometimes" - even though I can see the records are present when I use Query Analyzer.

The trick is, perhaps, that I am using a JAVA workflow system to query, so I need a bulletproof method to perform this query...

Here's the table:
USE [TransportData]
GO
/****** Object:  Table [dbo].[xml_til_trg_cockpit]    Script Date: 05/21/2012 12:15:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[xml_til_trg_cockpit](
      [id] [bigint] IDENTITY(1,1) NOT NULL,
      [xml] [text] COLLATE Danish_Norwegian_CI_AS NULL,
      [datotid] [datetime] NULL CONSTRAINT [DF_xml_til_trg_cockpit_datotid]  DEFAULT (getdate()),
      [imported] [tinyint] NULL CONSTRAINT [DF_xml_til_trg_cockpit_imported]  DEFAULT ((0)),
      [FBID] [varchar](50) COLLATE Danish_Norwegian_CI_AS NULL,
      [Importstamp] [datetime] NULL,
      [Bookcount] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_Bookcount]  DEFAULT ((0)),
      [KnNr] [varchar](50) COLLATE Danish_Norwegian_CI_AS NULL,
      [fragtnr] [varchar](50) COLLATE Danish_Norwegian_CI_AS NULL,
      [ordreid] [varchar](50) COLLATE Danish_Norwegian_CI_AS NULL,
      [koncernnr] [varchar](50) COLLATE Danish_Norwegian_CI_AS NULL,
      [importstatus] [tinyint] NULL CONSTRAINT [DF_xml_til_trg_cockpit_importstatus]  DEFAULT ((0)),
      [importnotes] [text] COLLATE Danish_Norwegian_CI_AS NULL,
      [cp_ordernr] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_cp_ordernr]  DEFAULT ((0)),
      [cp_fragtnr] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_cp_fragtnr]  DEFAULT ((0)),
      [comments] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_comments]  DEFAULT ((0)),
      [tt] [tinyint] NULL CONSTRAINT [DF_xml_til_trg_cockpit_tt]  DEFAULT ((0))
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

So I need to find all records where "datotid" >= '20120516 13:00:00' and datotid <= '20120517 13:00:00'

This works fine in Query analyzer, but not in my JAVA environment....

Is there another way - could I cast the "datotid" into two seperate, a date and a time - is that a good way to go ??
ASKER CERTIFIED SOLUTION
kmslogic

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros