Date function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date function

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-09-14 : 11:17:36

Hello,

i am trying to convert '2010-09-14'into SQL function date format.

IN SQL when i run the below script

delete from EMP_DAILY_SAL
where TOTAL_COUNT_DATE = '2010-09-14' ,all the rows with '2010-09-14' will be deleted.

what i actuall trying to do is instead of typing the date daily,i am looking for a sql function i can use in the place '2010-09-14'

when i run the script the records matching to that current date should be deleted.

guys,any ideas please let me know.

thanx in advance.

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-14 : 11:21:54

What is the datatype of TOTAL_COUNT_DATE ?

No, you're never too old to Yak'n'Roll if you're too young to die.

satya068
Posting Yak Master

233 Posts

Posted - 2010-09-14 : 11:24:33

quote:
Originally posted by webfred

What is the datatype of TOTAL_COUNT_DATE ?

No, you're never too old to Yak'n'Roll if you're too young to die.

thanx for ur reply.

datatype is date to the column TABLE_COUNT_DATE

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-14 : 11:38:58

So you are using SQL Server 2008 but you have posted in forum for 2005???

If 2008 then:

delete from EMP_DAILY_SAL
where TOTAL_COUNT_DATE = convert(date,getdate())

If not 2008 then again what is the datatype?

No, you're never too old to Yak'n'Roll if you're too young to die.

satya068
Posting Yak Master

233 Posts

Posted - 2010-09-14 : 11:43:31

quote:
Originally posted by webfred

So you are using SQL Server 2008 but you have posted in forum for 2005???

If 2008 then:

delete from EMP_DAILY_SAL
where TOTAL_COUNT_DATE = convert(date,getdate())

If not 2008 then again what is the datatype?

No, you're never too old to Yak'n'Roll if you're too young to die.

i am working on SQL 2005
datatype is datatime

thanx

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-14 : 11:48:40

I am not sure about the values in your table but this should work:

delete from EMP_DAILY_SAL
where dateadd(d,datediff(d,0,TOTAL_COUNT_DATE),0) = dateadd(d,datediff(d,0,getdate()),0)

!! -> It will delete all rows with the actual date in TOTAL_COUNT_DATE at runtime.

No, you're never too old to Yak'n'Roll if you're too young to die.

satya068
Posting Yak Master

233 Posts

Posted - 2010-09-14 : 12:17:34

quote:
Originally posted by webfred

I am not sure about the values in your table but this should work:

delete from EMP_DAILY_SAL
where dateadd(d,datediff(d,0,TOTAL_COUNT_DATE),0) = dateadd(d,datediff(d,0,getdate()),0)

!! -> It will delete all rows with the actual date in TOTAL_COUNT_DATE at runtime.

No, you're never too old to Yak'n'Roll if you're too young to die.

scripe worked.

WEB,
THANX FOR UR HELP

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-14 : 12:20:01

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-15 : 09:23:04

Another method

delete from EMP_DAILY_SAL
where
TOTAL_COUNT_DATE>= dateadd(day,datediff(day,0,getdate()),0) and
TOTAL_COUNT_DATE< dateadd(day,datediff(day,0,getdate()),1)

Madhivanan

Failing to plan is Planning to fail

satya068
Posting Yak Master

233 Posts

Posted - 2010-09-17 : 10:46:24

Thanx web, i can see the required result in the target table.

could you please explain how this script works

delete from EMP_DAILY_SAL
where
dateadd(d,datediff(d,0,TOTAL_COUNT_DATE),0) = dateadd(d,datediff(d,0,getdate()),0)

Thanx in advance.

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-17 : 10:57:18

DATETIME columns always have a time part.
You can see it if you run a SELECT getdate().
So if TOTAL_COUNT_DATE is for example 2010-09-17 00:00:00 and getdate() returns for example 2010-09-17 09:20:15 then the two dates are NOT equal because of the different time parts.
dateadd(d,datediff(d,0,getdate()),0) results in actual date with time part 00:00:00 so if you are doing that on both sides you can check if the dates are equal because time part is the same.

No, you're never too old to Yak'n'Roll if you're too young to die.

satya068
Posting Yak Master

233 Posts

Posted - 2010-09-18 : 06:58:56

Thanx web,

Satya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources