DateTime comparison - once more :(

trg_dk
trg_dk used Ask the Experts™
on
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 ??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?

Author

Commented:
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
use the following method

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

This never fails in comparison.

used 5 years with success ;-)
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
@gaurav2325 - In this case I also need the time, any ideas how to accomplish that ?
Commented:
Well I normally use a floor to do comparisons for a day, like

WHERE dateadd(day,datediff(day,0,MyDateColumn),0) = dateadd(day,datediff(day,0,@DayWeWant),0)

if MyDateField is "5/15/2012 20:37:22" then
dateadd(day,datediff(day,0,MyDateField),0)
will return a datetime of 5/15/2012 00:00:00.000

You seem to be counting a day as between 1pm and the next 1pm, so instead flooring your date directly you can floor your date -46800 seconds (subtracting the 1pm time from the date)

dateadd(day,datediff(day,0, dateadd(second, -46800, MyDateField)),0)

So...

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;

becomes


SELECT *, convert(varchar, datotid, 103) as ts from xml_til_trg_cockpit WHERE knnr = '20081' and (dateadd(day,datediff(day,0, dateadd(second, -46800, datotid)),0) = '20120516 00:00:00.000') ORDER BY fragtnr;
Try this for Time part

select convert(varchar,GETDATE(),109)

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

Author

Commented:
Works like a charm - thank you :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial