asked on

DateTime comparison - once more :(

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 ??

deighton

how are you passing the date and time to the query? Are you using datetime parameters, or are you building SQL as text

what sort of dates don't get included, can you give any examples?

trg_dk

ASKER

I'm building a T-SQL string using the JAVA env.

This is an example of a SQL query.

SELECT *, convert(varchar, datotid, 103) as ts from xml_til_trg_cockpit WHERE knnr = '20081' and (datotid >= '20120516 13:00:00' and datotid <= '20120517 13:00:00' ) ORDER BY fragtnr;

I would think that there would be a "native" datetime format in which I could pass a date and then make sure, that SQL Server understands it...
Like in MySql it's YYYY-MM-DD H:i:s

gaurav2325

use the following method

Convert(varchar,getdate(), 112) = '20120522' -- (YYYYMMDD)

This never fails in comparison.

used 5 years with success ;-)

trg_dk

ASKER

@gaurav2325 - In this case I also need the time, any ideas how to accomplish that ?

ASKER CERTIFIED SOLUTION

kmslogic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gaurav2325

Try this for Time part

select convert(varchar,GETDATE(),109)

select * from dimpromotion where convert(varchar,StartDate,109) < convert(varchar,GETDATE(),109)

trg_dk

ASKER

Works like a charm - thank you :)