t-sql question - 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)

t-sql question

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-02-18 : 17:11:45

I have a date1 field in the format: 2000-11-19 20:58:38.870
and I have a comments field with value of "data one yyy uuu 11/19/2000 8:58 PM",
I am trying to find if the date1 value matches with the datetimestamp of the comments field,
if matches then I want have a column of "Matches" as Yes/No, below is the sample data of date1 and comments fields:

date1 comments
2000-11-19 20:58:38.870 comment1 xxx yyyy 11/19/2000 8:58 PM
2000-11-20 08:13:05.230 comment2 aaa ccccbbbb 11/20/2000 8:13 AM
2000-11-22 08:20:05.230 comment4 aaa ccccbbbb 11/22/2000 8:45 AM
2000-11-21 08:15:05.230 comment6 aaa ccccbbbb 11/21/2000 8:15 AM

I want to get the output as follows:
date1 comments datePresentinComments Matches(Yes/No)

Thanks!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 18:35:55

what datatype is comments column? What about seconds portion in date1 column?

You can use substring,Charindex to see if it matches? But above has to be explained clearly.

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-18 : 19:03:35

Do you know that your date value will always occupy the very end position in your comments column?

So you need to cut the seconds and milliseconds off the Date1 value, and you need to extract the Date2 value from the Comments column.

cast(date1 as varchar) should effectively truncate seconds and milliseconds. Then convert it back to a datetime to be sure you're comparing date to date.

For the comments side of the comparison, first rtrim the comments value to make sure you don't have any trailing spaces, then grab the right-most 18 spaces and convert those to a datetime value.

...
where convert(datetime, cast(date1 as varchar)) = convert(datetime, right(rtrim(comments),18))

Hope that helps.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 20:04:20

[code]Select date1,comments,
(Case when Convert(datetime,convert(varchar(22),date1))= Convert(datetime, right(comments,len(comments)-Charindex(' ',comments,17)))
then 'YES' Else 'NO' End) as Matchdate
from YourTable[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-18 : 23:20:16

try this


select date1,comments, right(comments,18) as commentsdatetime,
case when Convert(datetime,convert(varchar(22),date1)) = convert(datetime,right(comments,18)) then 'y' else 'n' end as matches from urtablename

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 06:35:05

quote:

Originally posted by bklr

try this


select date1,comments, right(comments,18) as commentsdatetime,
case when Convert(datetime,convert(varchar(22),date1)) = convert(datetime,right(comments,18)) then 'y' else 'n' end as matches from urtablename

This won't work if data is like:

2000-11-19 22:58:38.870 comment1 xxx yyyy 11/19/2000 10:58 PM

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-19 : 08:13:52

k sodeep
i haven't check that query
see this one
select date1,comments, ltrim(right(comments,19)) as commentsdatetime,
case when Convert(datetime,convert(varchar(22),date1)) = convert(datetime,ltrim(right(comments,19))) then 'y' else 'n' end as matches from @tab

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources