July 14, 2012 at 2:35 am
Hi,
I have following table:
USE [Test_Shamshad]
GO
/****** Object: Table [dbo].[mTable1] Script Date: 07/14/2012 13:26:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mTable1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Dt] [varchar](50) NULL,
CONSTRAINT [PK_mTable1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
I used date and invalid date string values in this table to produce the scenario:
insert into mTable (name, dt)
select 'Name1', 'http://mysite.com' union
select 'Name2', 'test' union
select 'Name3', '12/12/2012' union
select 'Name4', '12/15/20102'
I am trying to get all rows from this table who has valid date input and entries are > GetDate()
I used following query which is giving me conversion error:
select id, Name, dt1 from (
select id, Name, convert(datetime, convert(varchar, dt, 107)) as dt1 from mTable1 WHERE (isdate (DT) = 1)
) t
where dt1 > GetDate()
it seems the convert method does not return converted column or the where IsDate() = 1 fuction preserve the actual table's column datatype which it won't compare with GetDate()
Please help.
Shamshad Ali.
July 14, 2012 at 3:00 am
i used following to fix my requirement
select id, Name, dt1, convert(varchar, GetDate(), 107) from (
select id, Name, convert(datetime,dt) as dt1 from mTable1 WHERE case when (isdate (DT) = 1) then dt else null end > GetDate()
) t
GO
-- to verify with old date try with back date entries:
insert into mTable1 (name, dt)
select 'Name5', '12/12/2011' union
select 'Name6', '12/12/2010'
GO
-- now gain run the query:
select id, Name, dt1, convert(varchar, GetDate(), 107) from (
select id, Name, convert(datetime,dt) as dt1 from mTable1 WHERE case when (isdate (DT) = 1) then dt else null end > GetDate()
) t
GO
Shamshad Ali
July 14, 2012 at 3:27 am
shamshad.ali (7/14/2012)
Hi,I have following table:
USE [Test_Shamshad]
GO
/****** Object: Table [dbo].[mTable1] Script Date: 07/14/2012 13:26:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mTable1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Dt] [varchar](50) NULL,
CONSTRAINT [PK_mTable1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
I used date and invalid date string values in this table to produce the scenario:
insert into mTable (name, dt)
select 'Name1', 'http://mysite.com' union
select 'Name2', 'test' union
select 'Name3', '12/12/2012' union
select 'Name4', '12/15/20102'
I am trying to get all rows from this table who has valid date input and entries are > GetDate()
I used following query which is giving me conversion error:
select id, Name, dt1 from (
select id, Name, convert(datetime, convert(varchar, dt, 107)) as dt1 from mTable1 WHERE (isdate (DT) = 1)
) t
where dt1 > GetDate()
it seems the convert method does not return converted column or the where IsDate() = 1 fuction preserve the actual table's column datatype which it won't compare with GetDate()
Please help.
Shamshad Ali.
Try this way.You need not to convert.
;WITH CTE AS(
SELECT id,NAME, CASE WHEN isdate (DT) = 1 THEN Dt ELSE '' END AS Dt
FROM mTable1)
SELECT * FROM CTE WHERE Dt <> '' and Dt > GETDATE()
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply