compare ntext to datetime

[curly]

Hi,

I have a log table where a date time is stored in an ntext field.
I need to take this date time and use it to search for other records logged at that date time.

I've tried using a subquery and comparing the datetime to the ntext but this obvioiusly wouldn't work. Converting the ntext to a datetime, nvarchar or varchar isn't working for me either. I get over flow and syntax errors.

example code:

SELECT * FROM tbl_log
WHERE date_time IN
(SELECT data FROM tbl_log)

This is probably a very simple and naive way of attempting it but my SQL knowledge is limited.

date_time (datetime) has the format 2008-04-01 08:13:49.000
and data (ntext) has the format 3/31/2008 2:34:44 PM

if I explicitly use the date as a string e.g. date_time IN ('3/31/2008 2:34:44 PM') the format is still comparable and it works but I need it as a query.

Thanks.

[ck9663]

past this in your query analyzer

declare @date_time smalldatetime
declare @tbldata table (rownum int, data ntext)
 
select @date_time = '2008-04-01 08:13:49.000'
 
insert into @tbldata (rownum,data) values (1,'3/31/2008 2:34:44')
insert into @tbldata (rownum,data) values (2,'4/01/2008 2:34:44')
 
select * from  @tbldata
 
select * from  @tbldata where datediff(dd,cast(cast(data as varchar(30)) as smalldatetime),@date_time) = 0

-- CK

[curly]

Thanks CK.

I tried this and it worked. I also added

AND datediff(ss,cast(cast(data as varchar(30)) as datetime),@date_time)=0 to compare the times too and it is working.

My next problem is how I apply this to my table and compare a list of dates. Each datetime we cast from data needs to be compared against each datetime in the table to see if there is a matching record. This is a log table and has columns:


DATE_TIME TYPE NAME DATA
2008-04-01 11:29:10.000 Print TIME_DATE 4/1/2008 9:33:35 AM
2008-04-01 14:09:13.000 Print TIME_DATE 4/1/2008 2:08:35 PM
.
.
.
2008-04-01 09:33:35.000 PrintInfo Description print sheet 1
2008-04-01 09:33:35.000 PrintInfo Quantity 4
2008-04-01 14:08:35.000 PrintInfo Description print letter
2008-04-01 14:08:35.000 PrintInfo Printer printerA

Some transactions record dates in data and these dates will have corresponding log entries and this is what I need to obtain. I hope this example shows what I'm trying to do and you might be able to help or steer me in the right direction.

[ck9663]

Sorry I'm a little slow today :)

Are you saying you have two tables? Could you post some sample data of your source table and the output you're trying to get?

-- CK

[curly]

I do have two tables but the first is easy so I haven't included it. I just get a date from it then use that date to search the second table, tbl_log, see my last post for the example. So say table1 gives me the date 2008-04-01 11:29:10.000
then I would use this in the second table tbl_log which would give me the data (ntext field) 4/1/2008 9:33:35 AM which I would use to query this same table again but against datetime i.e. data = datetime and (see further down the table) this should return the two records:

2008-04-01 09:33:35.000 PrintInfo Description print sheet 1
2008-04-01 09:33:35.000 PrintInfo Quantity 4

which is the information I need. I hope this is clearer. Thanks again.

[ck9663]

I do have two tables but the first is easy so I haven't included it. I just get a date from it then use that date to search the second table, tbl_log, see my last post for the example. So say table1 gives me the date 2008-04-01 11:29:10.000
then I would use this in the second table tbl_log which would give me the data (ntext field) 4/1/2008 9:33:35 AM which I would use to query this same table again but against datetime i.e. data = datetime and (see further down the table) this should return the two records:

2008-04-01 09:33:35.000 PrintInfo Description print sheet 1
2008-04-01 09:33:35.000 PrintInfo Quantity 4

which is the information I need. I hope this is clearer. Thanks again.

OK. Am still slow :)

Here goes...

select table1.field1, table1.field2, logtable.field1, logtable.field2
from table1 
left join logtable on datediff(dd,table1.yourdatefield,cast(cast(dateonlogtable as varchar(30)) as datetime)) = 0

-- CK

[curly]

Hi,
That wouldn't work for me since I need to query the log table twice. First to get the date from the data field using the table1 datetime and then again with the date from the data field. So I've decided on a much a simpler approach that provides me with the output I need.

1. get the data from logtable using table1 and cast this as a date and save it in a temporary table.
2. Use the temporary table to query the logtable.

 
SELECT cast(cast(data as varchar) as datetime) as date 
into #tempTable FROM logtable where date_time IN (
select date_time from table1) 
 
select logtable.field1, logtable.field2 from logtable, #tempTable
where #tempTable.date = logtable.date_time
 
 

Thanks for your help.