See if date is same year and month and day

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)

See if date is same year and month and day

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-03-08 : 16:58:07

Hi,

I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.

SO I need to do:

IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)

But it has to be comparing the year,month and day (excluding the time part of the datetime type).

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 17:08:23

[code]

Declare @d1 datetime, @d2 datetime

SET @D1 = getdate()
SET @D2 = dateadd(mi,-100,getdate())

Print @D1
Print @D2

Print Cast( DATEDIFF(d,dateadd(d,0,@D1),dateadd(d,0,@d2)) as varchar) + ' days different'
[/code]

Use: Datediff(d, dateadd(d,0,Created), dateadd(d,0,@Created) =0

OR

use dateadd(d,0,dateadd(d,0,Created)) = dateadd(d,0,dateadd(d,0,@Created))

Poor planning on your part does not constitute an emergency on my part.

praveen_balanagendra
Starting Member

2 Posts

Posted - 2008-03-08 : 21:09:46

Either of the following can be used

1. CONVERT(VARCHAR(10), Created, 101) .. this will get only the date part from date and timespan
2. YEAR(Created), MONTH(Created), DAY(Created) .. this will get year, month, day component of Created column

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 21:24:32

quote:
Originally posted by praveen_balanagendra

Either of the following can be used

1. CONVERT(VARCHAR(10), Created, 101) .. this will get only the date part from date and timespan
2. YEAR(Created), MONTH(Created), DAY(Created) .. this will get year, month, day component of Created column

and these would be more efficient than the date function I provided?

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 22:50:18

sql777...

this post:http://www.sqlteam.com/forums/post.asp?method=Edit&REPLY_ID=375621&TOPIC_ID=98678&FORUM_ID=30

these two aren't related, but you have interesting "needs" to work out....MVJ's comments in the other thread indicate potentially some design flaws...

Poor planning on your part does not constitute an emergency on my part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-09 : 01:24:53

quote:
Originally posted by sql777

Hi,

I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.

SO I need to do:

IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)

But it has to be comparing the year,month and day (excluding the time part of the datetime type).

Don't you have a primary key in the table ?

KH
[spoiler]Time is always against us[/spoiler]

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-03-09 : 07:54:01

khtan,

I will be inserting duplicate products in the table, but not for the same year/month/day.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 09:07:06

[CODE]
SET DATEFORMAT ymd
go
Insert INto ProductInfor,CreatedDate
Select a.ProductInfor,DATEADD(D,0,b.DATE)
FROM ProductTable a ,dbo.F_TABLE_DATE('20000101','200080229') b

WHERE NOT EXISTS (Select ProductInfo,dateadd(d,0,DateColumn FROM ProductTable)

[code]

Something like that may be easier..

F_TABLE_DATE : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Poor planning on your part does not constitute an emergency on my part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-09 : 10:10:32

[code]
WHERE NOT EXISTS
(
SELECT *
FROM myTable
WHERE Created >= dateadd(day, datediff(day, 0, @Created), 0)
AND Created < dateadd(day, datediff(day, 0, @Created), 1)
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 10:58:08

quote:

Originally posted by khtan


WHERE NOT EXISTS 
      (
          SELECT * 
          FROM   myTable 
          WHERE  Created >= dateadd(day, datediff(day, 0, @Created), 0)
          AND    Created <  dateadd(day, datediff(day, 0, @Created), 1)
      )

Thanks for the edit..:)

KH
[spoiler]Time is always against us[/spoiler]

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources