469,645 Members | 1,984 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

compare ntext to datetime

4
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.
Jul 30 '08 #1
6 6588
ck9663
2,878 Expert 2GB
past this in your query analyzer

Expand|Select|Wrap|Line Numbers
  1. declare @date_time smalldatetime
  2. declare @tbldata table (rownum int, data ntext)
  3.  
  4. select @date_time = '2008-04-01 08:13:49.000'
  5.  
  6. insert into @tbldata (rownum,data) values (1,'3/31/2008 2:34:44')
  7. insert into @tbldata (rownum,data) values (2,'4/01/2008 2:34:44')
  8.  
  9. select * from  @tbldata
  10.  
  11. select * from  @tbldata where datediff(dd,cast(cast(data as varchar(30)) as smalldatetime),@date_time) = 0

-- CK
Jul 30 '08 #2
curly
4
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.
Jul 31 '08 #3
ck9663
2,878 Expert 2GB
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
Jul 31 '08 #4
curly
4
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.
Aug 1 '08 #5
ck9663
2,878 Expert 2GB
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...
Expand|Select|Wrap|Line Numbers
  1. select table1.field1, table1.field2, logtable.field1, logtable.field2
  2. from table1 
  3. left join logtable on datediff(dd,table1.yourdatefield,cast(cast(dateonlogtable as varchar(30)) as datetime)) = 0
-- CK
Aug 1 '08 #6
curly
4
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT cast(cast(data as varchar) as datetime) as date 
  3. into #tempTable FROM logtable where date_time IN (
  4. select date_time from table1) 
  5.  
  6. select logtable.field1, logtable.field2 from logtable, #tempTable
  7. where #tempTable.date = logtable.date_time
  8.  
  9.  
Thanks for your help.
Aug 6 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by conckrish | last post: by
3 posts views Thread by Mark | last post: by
3 posts views Thread by =?Utf-8?B?ZGF2aWQ=?= | last post: by
3 posts views Thread by ChrisB | last post: by
3 posts views Thread by =?Utf-8?B?U2FuZHBvaW50R3V5?= | last post: by
25 posts views Thread by Brian | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.