how do get difference of datetime in sql 2005

Posted by Kannan1986 under Sql Server on 2/17/2010 | Views : 1336 | Status : [Member] | Replies : 6
hi,

how do get difference of datetime in sql 2005

ex:

i have table with 50 rows... i have to delete the rows if the datetime field in the table reaches the 16 th day..




Responses

Posted by: Vuyiswamb on: 2/17/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
ok when it Reaches the 16th Day of the month ?

Thank you for posting at Dotnetfunda
[Administrator]

Kannan1986, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kannan1986 on: 2/17/2010 [Member] Starter

Up
0
Down
i have to delete that row when the datetime field in the table reaches the 16 th day

Kannan1986, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 2/17/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
ok, i have Compiled an example for you, one more question. Do you want to delete all the Rows that has the Date 16 and higher. This means you want to leave the record with the date starting from 15 to lower?

Thank you for posting at Dotnetfunda
[Administrator]

Kannan1986, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 2/17/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
if that is the Case then you can test this by First Finding the Records that are 16 or higher like this

CREATE TABLE #TEMP
(
FNAME VARCHAR(30),
LASTNAME VARCHAR(30),
DATE DATETIME
)

INSERT INTO #TEMP
VALUES('VUYISWA','MASEKO','1 June 2010')


INSERT INTO #TEMP
VALUES('karen','Kock','10 June 2010')


INSERT INTO #TEMP
VALUES('Theresa','Sharp','15 June 2010')

INSERT INTO #TEMP
VALUES('Levy','Themna','15 June 2010')

INSERT INTO #TEMP
VALUES('Paul','Putter','6 June 2010')


INSERT INTO #TEMP
VALUES('Ivam','Mayer','17 June 2010')



INSERT INTO #TEMP
VALUES('Sheo','Narayan','17 June 2010')



INSERT INTO #TEMP
VALUES('Sagar','Sagar','16 June 2010')



INSERT INTO #TEMP
VALUES('Kumar','Kumar','16 June 2010')

select * from #TEMP
where DATEPART(day, date) >= 16


after you got them if you want to delete them you can run this

delete #temp
where DATEPART(day, date) >= 16


Tell me if this is what you want.

Thank you for posting at Dotnetfunda

Vuyiswa Maseko



Thank you for posting at Dotnetfunda
[Administrator]

Kannan1986, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kannan1986 on: 2/17/2010 [Member] Starter

Up
0
Down
no...

i have to delete all the records when the datetime reach 16 th day...

i solved that problem...

delete from temporary_Register where ( getdate() - date_time) >= 16

Thanks for your kind reply...


Thanks & Regards,

Kannan.S

Kannan1986, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 2/17/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
mark your Answer as "Resolved"

Thank you for posting at Dotnetfunda
[Administrator]

Kannan1986, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response