Avatar of trg_dk
trg_dk
Flag for Denmark 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 ??
Microsoft SQL Server

Avatar of undefined
Last Comment
trg_dk

8/22/2022 - Mon
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 ;-)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
trg_dk

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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 :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.