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
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_da
[imported] [tinyint] NULL CONSTRAINT [DF_xml_til_trg_cockpit_im
[FBID] [varchar](50) COLLATE Danish_Norwegian_CI_AS NULL,
[Importstamp] [datetime] NULL,
[Bookcount] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_Bo
[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_im
[importnotes] [text] COLLATE Danish_Norwegian_CI_AS NULL,
[cp_ordernr] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_cp
[cp_fragtnr] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_cp
[comments] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_co
[tt] [tinyint] NULL CONSTRAINT [DF_xml_til_trg_cockpit_tt
) 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 ??
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
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_da
[imported] [tinyint] NULL CONSTRAINT [DF_xml_til_trg_cockpit_im
[FBID] [varchar](50) COLLATE Danish_Norwegian_CI_AS NULL,
[Importstamp] [datetime] NULL,
[Bookcount] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_Bo
[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_im
[importnotes] [text] COLLATE Danish_Norwegian_CI_AS NULL,
[cp_ordernr] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_cp
[cp_fragtnr] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_cp
[comments] [int] NULL CONSTRAINT [DF_xml_til_trg_cockpit_co
[tt] [tinyint] NULL CONSTRAINT [DF_xml_til_trg_cockpit_tt
) 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 ??
Last Comment
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
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(),
This never fails in comparison.
used 5 years with success ;-)
Convert(varchar,getdate(),
This never fails in comparison.
used 5 years with success ;-)
ASKER
@gaurav2325 - In this case I also need the time, any ideas how to accomplish that ?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Try this for Time part
select convert(varchar,GETDATE(),
select * from dimpromotion where convert(varchar,StartDate,
select convert(varchar,GETDATE(),
select * from dimpromotion where convert(varchar,StartDate,
ASKER
Works like a charm - thank you :)
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
what sort of dates don't get included, can you give any examples?