6

How to compare two dates the first column table one 2013-04-04 05:47:52.000 the second one from other table 2010-01-01 00:00:00.000. I want to compare just yy/month/day; if they are equal, I get second id table.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
user2586714
  • 149
  • 1
  • 1
  • 7

3 Answers3

9

For Sql Server you can do this:

CAST(table1date AS DATE) = CAST(table2date AS DATE)

Example of how it could be used:

declare @dateTime1 as datetime = '2013-04-04 05:47:52.000'
declare @dateTime2 as datetime = '2013-04-04 00:00:00.000'

if CAST(@dateTime1 AS DATE) = CAST(@dateTime2 AS DATE)
    print 'yy mm dd is the same'
else
    print 'not the same'

Or using tables:

declare @dateTime1 as datetime = '2013-04-04 05:47:52.000'
declare @dateTime2 as datetime = '2011-04-04 00:00:00.000'

declare @table1 table (id1 int, dt1 datetime)
declare @table2 table (id2 int, dt2 datetime, table1id int)
insert into @table1 values (1, @dateTime1)
insert into @table2 values (2, @dateTime2, 1)

select case when CAST(@dateTime1 AS DATE) = CAST(@dateTime2 AS DATE) then t2.id2 else t2.table1id end as id
from @table1 t1 join @table2 t2 on t1.id1 = t2.table1id
PostureOfLearning
  • 3,481
  • 3
  • 27
  • 44
  • i use this database http://stackoverflow.com/questions/17743900/fact-table-ssis-sqlserver-r2008 – user2586714 Jul 22 '13 at 23:35
  • i tried this code but i get error: Msg 4104, Level 16, State 1, Line 4 The multi-part identifier "dbo.FACT_TABLE.DATE_DEBUT_ALRM" could not be bound. Merge fact_table as target using( select top 1 idDateDeb,PK_Date, Week_Of_Year from dbo.dim_date_Debut)as source on cast(source.PK_Date as date)=cast(dbo.FACT_TABLE.DATE_DEBUT_ALRM as date) when matched then update set target.ID_TEMP_DEB=source.idDateDeb; – user2586714 Jul 22 '13 at 23:54
  • @user2586714, I'm not sure how you are using the code, but I've added a working example. See if that helps – PostureOfLearning Jul 23 '13 at 00:17
  • i want to do triggers when data adding on fact_table id_date_deb and id_date_fin inserted by comparing date from fact_table and calendar and get Id and numberOfWeek – user2586714 Jul 23 '13 at 00:23
  • @user2586714, does the second example using tables help? – PostureOfLearning Jul 23 '13 at 00:27
  • @user2586714, well, this question only asks to compare 2 dates from 2 different tables and getting the id from one of the table depending on the date comparison. I believe my second example does exactly this and answers this question. You are now asking me to answer a different post which is not relevant to this question. I'll have a closer look at your other post, but as far as I can tell, this has been answered. – PostureOfLearning Jul 23 '13 at 00:41
5
IF CAST(DateField1 AS DATE) = CAST(DateField2 AS DATE)
Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
Saritha.S.R
  • 800
  • 1
  • 6
  • 19
4

By compare you mean to find the difference?

How about

DATEDIFF(datepart,startdate,enddate)

http://www.w3schools.com/sql/func_datediff.asp

oliver_siegel
  • 1,666
  • 3
  • 22
  • 37