473,322 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

To Retrieve date stored in type INT

6
hi ,

i have a problem, here is the issue....
i have table with date stored as int.....say for
eg : date: 11/30/2006 time:09:15 am is stored in the table as 1164896114
now i need to compare this date and pull out the transaction related to this date.....please tell me the syntax to be used , it would be great!

select * from table name where datefield = "11/30/2006" (recorrect it the syntax for me please )
Dec 1 '06 #1
12 5336
willakawill
1,646 1GB
hi ,

i have a problem, here is the issue....
i have table with date stored as int.....say for
eg : date: 11/30/2006 time:09:15 am is stored in the table as 1164896114
now i need to compare this date and pull out the transaction related to this date.....please tell me the syntax to be used , it would be great!

select * from table name where datefield = "11/30/2006" (recorrect it the syntax for me please )
Hi. Your table will likely be storing datetime fields so the above syntax may not work as you are not specifying a time. If you want to search for a specific date you should use the following syntax:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM table_name
  2. WHERE datefield >= '11/30/2006'
  3. AND datefield < '11/31/2006'
Dec 2 '06 #2
blast
6
Hi. Your table will likely be storing datetime fields so the above syntax may not work as you are not specifying a time. If you want to search for a specific date you should use the following syntax:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM table_name
  2. WHERE datefield >= '11/30/2006'
  3. AND datefield < '11/31/2006'
hi ,

thks a lot for your reply, as i run using the above syntax it gives me this error printed

" Syntax error converting the varchar value '11/30/2006' to a column of data type int."

please guide me further, it would be of great help, thks
Dec 4 '06 #3
iburyak
1,017 Expert 512MB
How 11/30/2006 time:09:15 am became 1164896114?

I would assume it should be 113020060915
Dec 4 '06 #4
blast
6
How 11/30/2006 time:09:15 am became 1164896114?

I would assume it should be 113020060915
hi,

im not sure why it shows 1164896114 instead of 113020060915 , its data type is int...........i have no idea why it has been stored in the above format........
Dec 4 '06 #5
iburyak
1,017 Expert 512MB
The thing is you have to be able to undo int to date_time first.

But if you don't know how it was encrypted into this number not sure how to help you.
Dec 4 '06 #6
iburyak
1,017 Expert 512MB
Are you sure it is a right column you are looking at for date time?
What other columns you have in a table?
It looks like some ID number.
Dec 4 '06 #7
almaz
168 Expert 100+
eg : date: 11/30/2006 time:09:15 am is stored in the table as 1164896114
can you give several date/value pairs (i.e. "11/30/2006 time:09:15 am corresponds to 1164896114"), so that we can deduce the logic of converting int to datetime data type
Dec 5 '06 #8
Killer42
8,435 Expert 8TB
can you give several date/value pairs (i.e. "11/30/2006 time:09:15 am corresponds to 1164896114"), so that we can deduce the logic of converting int to datetime data type
Yes, because it sounds as though what you really need is to create a function to convert a date/time value to this apparently proprietary format, for comparison. Or more likely, reuse one which already exists somewhere, since the stored values must have come from somewhere.

Being able to convert the other way, from the Int to date/time, might be handy for display and also interesting, but wouldn't really help with the SQL search string.
Dec 5 '06 #9
I imagine it could be done with simple math... depending how your date/time is stored, if it is using seconds, miliseconds, etc.

If it was using seconds only, then you would subtract the dates, then divide by 60 (for seconds to give you minutes, 3600 to give you hours, or 86400 to give you days), I think... I am doing this in my head, so my math may be off, but that's all dates are: Integers...

So if the difference is 60 (seconds), then you know what to do.

Good luck,

Michael C. Gates
Dec 6 '06 #10
Killer42
8,435 Expert 8TB
If it was using seconds only, then you would subtract the dates, then divide by 60 (for seconds to give you minutes, 3600 to give you hours, or 86400 to give you days), I think... I am doing this in my head, so my math may be off, but that's all dates are: Integers...
Well yes, but that's only half the story. A date/time field generally holds a date as the integer part and the time as the decimal part. At least in VB and Access that's the case. I would imagine it's pretty similar in SQL Server.
Dec 6 '06 #11
Well yes, but that's only half the story. A date/time field generally holds a date as the integer part and the time as the decimal part. At least in VB and Access that's the case. I would imagine it's pretty similar in SQL Server.
Ahhh, didn't think of that. Glad you put that in my mind... Thanks for clarifying.

MS SQL stores it in Date/Time format. I guess there is a way to get the Int value, or Decimal value, but I think you have to convert it. I would personally add a new field, copy the values to it, and then re-add the date/time field correctly formatted.

Michael C. Gates
Dec 6 '06 #12
Killer42
8,435 Expert 8TB
hi ,

i have a problem, here is the issue....
i have table with date stored as int.....say for
eg : date: 11/30/2006 time:09:15 am is stored in the table as 1164896114
now i need to compare this date and pull out the transaction related to this date.....please tell me the syntax to be used , it would be great!

select * from table name where datefield = "11/30/2006" (recorrect it the syntax for me please )
Just been doing a bit of speculative stuff, and it looks to me as though this may be a number of seconds since 01/01/1970. Working under that assumption, here's a function to return the value. It's in VB6 (straight from my head, not tested) but you should be able to translate if required.
Expand|Select|Wrap|Line Numbers
  1. Public Function DateToInt(ByVal pDate As Date) As Long
  2.   ' Long because too large for an Integer in VB. Unsure about other languages.
  3.   DateToInt = DateDiff("s", #1/1/1970#, pDate)
  4. End Function
  5.  
Then in your SQL use
Expand|Select|Wrap|Line Numbers
  1. select * from table name where datefield = DateToInt(Cdate("11/30/2006"))
Obviously you might want to adjust things - for example, accepting a string rather than a date value, etc.
Dec 6 '06 #13

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

Similar topics

2
by: forums_mp | last post by:
I've got an STL class (see below) with two functions to store and retrieve data - msg structs. The "Store" function when called will copy the received message (depending on which message) into...
4
by: Dale | last post by:
Hi Everyone, I've got a form that provides a pop-up calendar for users to select dates for requesting jobs to be completed. The calendar works great, but it unfortunately allows users to select...
0
by: george_Martinho | last post by:
It seems that the ASP.NET Microsoft team didn't think about this!! The profilemanager class has the following methods: - DeleteInactiveProfiles. Enables you to delete all profiles older than a...
2
by: michele | last post by:
Hi, I want to load a listbox with the date that are stored in a database (mdb), the problem is when I retrieve the date it has a format like "01/01/2006 0.00.00". In Access the date is stored as...
13
by: kev | last post by:
Hi all, I have created a database for equipments. I have a form to register the equipment meaning filling in all the particulars (ID, serial, type, location etc). I have two buttons at the end...
4
by: Simon Gare | last post by:
Hi all, I am trying to retrieve a count of booking entries made 30 days ago, below is the end of the query I am having problems with. dbo.booking_form.TimeOfBooking = DATEADD(day, -30,...
1
by: stuck1512 | last post by:
hi, i was writing an apache module & successful in retrieving archived files from a folder. i compared all file properties before retrieving but the problem is now i have to retrieve them as...
7
by: =?Utf-8?B?UVNJRGV2ZWxvcGVy?= | last post by:
I have a C# logging assembly with a static constructor and methods that is called from another C# Assembly that is used as a COM interface for a VB6 Application. Ideally I need to build a file...
2
by: Cramer | last post by:
Using ASP.NET 3.5... As far as I know, any time we store a value in application or session state, it is stored as a humble 'object' type rather than it's "real" type. For example, if we want...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.