Comparing Dates - 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 2008 Forums

Transact-SQL (2008)

Comparing Dates

Author

Topic

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-02-07 : 14:41:20

I have a date that is concatenated with another field to create a unique column (this column came from Access as an data import)

for example:

namehere2/7/2011 8:30:33 PM

So in SQL i'm trying to recreate this by concatenating the name column with the date column, since i have the two neccessary fields. Then i want to do a join on the two fields. However, I can't get my SQL field to match the field above.

for exampe, I came come up with something like this in SQL

namehereFeb 07 2011  8:30:000AM

but i can't join these two fields because they're not the same?!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 16:16:36

My guess is that during your import, SQL does this:
Select cast('Feb 07 2011 8:30:33 AM' as datetime) ---SQL does this
--2011-02-07 08:30:33.000

Which you can then use the below to make it appear, in string, like the one in access...for your concatenation.

Select 'namehere' + REPLACE(CONVERT(varchar(26),cast('2011-02-07 08:30:33.000' as datetime),109),':000',' ')
--namehereFeb 7 2011 8:30:33 AM

However, You should consider leaving them separate and make a unique index around the 2 original columns instead if you have that choice.
You can use something like this on the source column..to make it match in the update statement or select you are using to create the new field.

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

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-02-08 : 14:30:41

Well, really what i'm trying to do is make:

namehereFeb 07 2011  8:30:000AM

= (equal to)

namehere2/7/2011 8:30:33 AM

namehereFeb 07 2011  8:30:000AM

--(above)is an output from SQL (that I create so that I can match it to the imported data)

namehere2/7/2011 8:30:33 AM

--(above)is imported data that i cannot change

Both of these should be exactly the same, but when i try to join these two columns I get nothing because the data is not the same...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-08 : 15:31:32

I'd wait until one of the smart people reply, usually within minutes of my post, but here's something
declare @name varchar(20)
declare @date datetime
declare @tmpDate varchar(20)
set @name = 'namehere'
set @date = dateadd(hour,7,getdate())
set @tmpdate = convert(varchar(20),@date,101)

select @name+stuff(stuff(@tmpdate,1,1,''),3,1,'')+ ' '+
right(convert(varchar(30),@date,100),8)

Jim

Everyday I learn something that somebody else already knew

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-09 : 01:03:40

why don't you just convert the "2/7/2011 8:30:33 PM" into datatime and use it for your matching ?

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-09 : 01:15:48

quote:
Originally posted by Brittney10

Well, really what i'm trying to do is make:

namehereFeb 07 2011 8:30:000AM
= (equal to)
namehere2/7/2011 8:30:33 AM

1) only way to make it equal is to truncate a portion of the data which is problematic e.g. in this case its the "seconds" portion.

2) convert both sources to datetime or varchar of same type

e.g. convert(varchar(15),source1DateField,105)=convert(varchar(15),source2DateField,105)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources