Somebody2


I have a table with a bunch of rows and each row has a datetime when the transaction occurs. Is it possible to break the date off of the datetime I want to select some of the rows if they occur between one date and another date, is it possible


Re: Split datetime()

Alex Feldstein


Use TTOD()

dMyDate = Ttod(dtMyDateTime)

See:

http://msdn2.microsoft.com/en-us/library/kbd3t50t(VS.80).aspx






Re: Split datetime()

Naomi Nosonovsky

See also http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1792297&SiteID=1 if you have an existing index on the datetime field you want to use. E.g. dtot(DateField+1) - 1 gives you one second before midnight of the following date.




Re: Split datetime()

Somebody2

That seems to work fine, but an select statement doesn't seem to work.

I just used one of the dates in the table.

SELECT amount FROM balances WHERE TTOD(date) = 06/26/07 INTO ARRAY amtinfo

I get an error.





Re: Split datetime()

CetinBasoz

Select amount from balances where ttod(date) = {^2007/07/26} into array amtInfo

Select amount from balances where ttod(date) = date(2007,7,26) into array amtInfo

...





Re: Split datetime()

Naomi Nosonovsky

What index do you have on the date field, if you have an index

If you don't then you may use your select with the correct date constant {^2007-06-27}. If you do have an index on the time field, then you may use StartTime as datetime(2007,6,26) and EndTime as datetime(2007,6,26,23,59,59)





Re: Split datetime()

dni

You may try something like:

{^2007/06/26}






Re: Split datetime()

Somebody2

And this will work if I have the date as a variable. I could use {^date}



Re: Split datetime()

CetinBasoz

No with a date variable you would simply put your variable there:

myDateVariable = date(2006,3,20) && just picked a sample date

select * from myTable where ttod(myDateTimeField) = m.myDateVariable





Re: Split datetime()

Somebody2

Operator/operand type mismatch.

I have the user type in a date, like 2007/06/26 and then it takes that date and performs the following -

SELECT amount FROM balances WHERE TTOD(date) = startdate INTO ARRAY tinfo

startdate would equal 2007/06/26





Re: Split datetime()

CetinBasoz

Type in Where does he type that in What is the datatype of your variable

If you're using a textbox for users to type in and datatype is character (default) then you need a conversion to date format first. However users might type in deliberate dates in various formats and it might be hard to parse for all of those formats. Instead use a textbox which you set its value to {}. It then would automatically accept date data only (using current date settings - and settings change wouldn't affect you). You could use that value directly. ie:

select amount from balances where ttod(date) = thisform.txtDate.Value into array tinfo

You might provide the user a calendar too (ie: DatetimePicker activex - its value is datetime).





Re: Split datetime()

Naomi Nosonovsky

What is the type of the value of the textbox It looks to me you're using character variable instead, hence the error.

e.g. lcDate = '2007/12/13'

ldDate = DATE(VAL(STREXTRACT(m.lcdate,'','/',1)),VAL(STREXTRACT(m.lcdate,'/','/',1)),VAL(STREXTRACT(m.lcdate,'/','',2)))

Or do it with substr of the year, month, date instead.