To Retrieve date stored in type INT

[blast]

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 )

[willakawill]

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:

SELECT * FROM table_name
WHERE datefield >= '11/30/2006'
AND datefield < '11/31/2006'

[blast]

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:

SELECT * FROM table_name
WHERE datefield >= '11/30/2006'
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

[iburyak]

How 11/30/2006 time:09:15 am became 1164896114?

I would assume it should be 113020060915

[blast]

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........

[iburyak]

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.

[iburyak]

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.

[almaz]

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

[Killer42]

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.

[gateshosting]

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

[Killer42]

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.

[gateshosting]

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

[Killer42]

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.

Public Function DateToInt(ByVal pDate As Date) As Long
  ' Long because too large for an Integer in VB. Unsure about other languages.
  DateToInt = DateDiff("s", #1/1/1970#, pDate)
End Function

Then in your SQL use

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.