October 7, 2014 at 2:05 pm
I have a table which has manufacturing date column but they use the varchar instead of datetime datatype.
Also that has the null values.
I need to get the id values which has manufacturing date has greater than 2 yrs from todays date.
i.e. older than 2012
i.e. I want to delete all the records which is greater than 2 yr old.
Manufactured date has date dd/mm/yy format
DECLARE @Update VARCHAR(10)
SET @Update = convert(VARCHAR ,DATEADD(YEAR,-2,getdate()),3)
select @update
select
id, MANUFACTURED_DATE
from dbo.TestTable
WHERE convert (varchar(30),MANUFACTURED_DATE, 1) < @Update
But I am getting all the dates which has manufacturing dates in 2013 & 2014 also
October 7, 2014 at 2:18 pm
Convert the varchar fields to datetime fields, and then do the comparison.
You will need to use the style of 3 when you convert the varchar date values.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 7, 2014 at 2:42 pm
ramana3327 (10/7/2014)
I have a table which has manufacturing date column but they use the varchar instead of datetime datatype.
You'll need to convert them then compare as the easiest method. Say goodbye to hoping to use indexing here unless you persist a computed column.
EDIT: Missed the d/m/y instead of m/d/y
DECLARE @badDate VARCHAR(10)
SET dateformat dmy
SET @badDate = '13/02/11'
--SET @badDate = NULL
SELECT
CONVERT( DATETIME, @badDate) AS convertedDate,
DATEADD( yy, -2, GETDATE()) AS twoYearsAgoToday,
CASE WHEN CONVERT( DATETIME, @badDate) < DATEADD( yy, -2, GETDATE())
THEN 1
ELSE 0
END AS IsOverTwoYears
If you want to default nulls, just check that before you do any converts.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 7, 2014 at 7:19 pm
Yes,
I am converting from varchar to datetime
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply