deletion with differnt date format

Question

hi

I haveone table vicidial_list i need to do delete from it based on getdate -1

but the problem is both the date format is different.

Like

select top 1 modify_date from vicidial_list       01-01-2013 02:01:59
select (GETDATE())                                      2013-02-15 13:37:31.250

I need to use

delete from vicidial_list

where modify_date=getdate()

Kindly help

Answer 1

Try the below depends on your dataype of the datecolumn

Drop table T1
Create Table T1(Val Date)
Insert into T1 Select '01-01-2013 02:01:59'
delete From T1 Where Val = '2013-01-01 02:01:59' --Deletes
Drop table T1
Create Table T1(Val varchar(100))
Insert into T1 Select '01-01-2013 02:01:59'
Delete From T1 Where Cast(Val as DATE) = '2013-01-01 02:01:59' --Delete

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

hi

I haveone table vicidial_list i need to do delete from it based on getdate -1

Try

delete from vicidial_list

where convert(varchar,modify_date,112)=convert(varchar,dateadd(dd,-1,getdate()),112);

---

Many Thanks & Best Regards, Hua Min

Answer 3

Hi BI_group,

Have a look at this,

SELECT Cast(Getdate() - 1 AS DATE) --- 2013-02-14 (getdate -1 -- yesterday)

SELECT CONVERT(DATE, '02-14-2013 02:01:59', 101) -- 2013-02-14 (date in table vicidial_list)
CREATE TABLE T1
  (
     Val DATETIME
  )

INSERT INTO T1
SELECT '02-14-2013 02:01:59'

SELECT *
FROM   t1

DELETE FROM T1
WHERE  CONVERT(DATE, val, 101) = Cast(Getdate() - 1 AS DATE)

SELECT *
FROM   t1

DROP TABLE T1 

Thanks,

---

Dineshkumar

Answer 4

Convert(varchar(10),date1,121)=convert(varchar(10),getdate()-1,121)

---

-- Bhavesh Thakkar

Sr.Database Administrator
My Blog
Linked In Profile

Answer 5

delete from Vicidial_list

where Modify_date >=dateadd(day,-1 +datediff(day, '19000101', CURRENT_TIMESTAMP),'19000101') -- yesterday

and Modify_Date< dateadd(day, datediff(day, '19000101', CURRENT_TIMESTAMP),'190001010')

See this blog post

Bad habits to kick : mis-handling date / range queries

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

The DATE data type is an internal format. Apparently, you are using both the old DATETIME and VARCHAR(n) for dates. That is an awful way to write SQL and you should fire whoever did this. Also, we use CAST (CURRENT_TIMESTAMP AS DATE) today and not the old 1970's Sybase/UNIX  getdate() function call.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL