473,583 Members | 2,878 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

compare ntext to datetime

4 New Member
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 7326
ck9663
2,878 Recognized Expert Specialist
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 New Member
Thanks CK.

I tried this and it worked. I also added

AND datediff(ss,cas t(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 Recognized Expert Specialist
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 New Member
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 Recognized Expert Specialist
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 New Member
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

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

Similar topics

5
18516
by: Cally | last post by:
Hello, I would like to convert a field from ntext field found in one database table to float field found in another database table. The reason why I want to do this is a long one. I have tried the following and playing around with the following: declare @valuePointer varbinary(16)
5
6974
by: Tom | last post by:
It appears that you can't compare two dates in DotNet. You must use ToString and compare the strings. Is that the only reliable way? Try this: Dim dteOne As Date = FileDateTime(Application.ExecutablePath) Dim dteTwo As Date = FileDateTime(Application.ExecutablePath) SaveSetting("Test", "Dates", "DateThree",...
12
61196
by: conckrish | last post by:
Hi all.. Can anyone tell me how to compare datetime objects?I ve three objects namely Current date,start date and end date.. I need to check the current date with Start date and end date....Plz tell me how to compare ??? Thanx.. Krish
3
28480
by: Mark | last post by:
I'd like to compare two datetime values in milliseconds. The datetime.compare method appears to show only seconds. Milliseconds of a datetime are available as a property of each datetime, but I am assuming there is an easier way than comparing the milliseconds, seconds, minutes, hours, days, etc, individually to generate the difference. ...
3
2931
by: =?Utf-8?B?ZGF2aWQ=?= | last post by:
I try to follow Steve's paper to build a database, and store a small text file into SQL Server database and retrieve it later. Only difference between my table and Steve's table is that I use NTEXT datatype for the file instead of using IMAGE datatype. I can not use SqlDataReader to read the data. I need your help, Thanks. -David (1) I...
6
2176
by: xkenneth | last post by:
Looking to do something similair. I'm working with alot of timestamps and if they're within a couple seconds I need them to be indexed and removed from a list. Is there any possible way to index with a custom cmp() function? I assume it would be something like... list.index(something,mycmp) Thanks!
3
6203
by: ChrisB | last post by:
Hello, I was wondering what the easiest way is to compare two DateTime objects and not have the time components be included in the comparision. For, example, if time1 = 10/01/07 9:00 am, and time2 = 10/1/07 10:00 am, time1.CompareTo(time2) == 0 should evaluate to true. Thanks, Chris
3
2720
by: =?Utf-8?B?U2FuZHBvaW50R3V5?= | last post by:
string dateTime = "5/28/2008"; DateTime.Compare(DateTime.Now,DateTime.Now) // returns 0 DateTime.Compare(DateTime.Now,DateTime.Parse(dateTime)) // returns 1 I need to compare 2 dates, using the second format, and I dont understand why it isnt returning 0 Thank you
25
4528
by: Brian | last post by:
I have a datetimepicker formated for just time, the user selects the time. I want to compare if that time is between midnight and 8 am dtmTime #11:59:59 PM# and dtmTime < #08:00:00 AM# this evaluates to true when the time is not greater than dtmTime #9:32:34 PM# Why is that? and How can I get this to work? Brian
0
7888
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7811
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8159
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
6571
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5689
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5366
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3811
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2317
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1147
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.