Problems with Time/Date

[chuckh70]

This is the last issue on my plate and it is driving me insane so any help what so ever would be highly appreciated.

I have a page I wrote that needs to allow for a list box that allows the user to select Daily, Weekly, Monthly. The Time / Date Field in the DB is UTC, and I need to check against that.

So in my ChkDay Function i'd like to accomplis the following.

if daily - get today in utc and return all records that match.
if weelky - get the utc and search on a range
Monthly - Same as weekly.


I managed to get today's UTC but for the life of me can't
figure out how to strip it down to just the date..

I striped it down, but here's the start of the function.

Sub GetChkDay(Sender As Object, E As EventArgs) 

Dim dblTtamp As Double = System.Math.Floor( DateTime.Now.Subtract(New DateTime(1970, 1, 1)).TotalSeconds ) ' This gives me the UTC by using 1 line of code

'This breaks it down
Dim dtUTC As DateTime = DateTime.Now

Dim dtStart As New DateTime(1970, 1, 1)

Dim tsDifference As TimeSpan = dtUTC.Subtract(dtStart)

'convert time as CTime

'figure out what the week or month span would be

If MyList.SelectedItem.Value="day" Then
connection info
select * from table where @CTime =""
Else
 If MyList.SelectedItem.Value="wkly" Then
connection info
select * from table where timedate Between "@CTime" and "@CTime2"
Else
 If MyList.SelectedItem.Value="mon" Then
connection info
select * from table where timedate Between "@CTime" and "@CTime3"       
End Sub

the list box would be as follows...

<asp:dropdownlist id="MyList"  runat="server" 
Font-Size="XX-Small" OnLoad="ChkDay" >
<asp:ListItem Value="*" Selected=True>
All Open Tickets</asp:ListItem>
<asp:ListItem Value="day">Daily</asp:ListItem>
<asp:ListItem Value="wkly">Weekly</asp:ListItem>
<asp:ListItem Value="mon">Monthly</asp:ListItem>
</asp:dropdownlist><FONT size="1"></FONT>

Again any help would be greatly appreciated

 

[checkai]

if you used date.now.date.toUniversalTime would that work any better?

dlc

 

[chuckh70]

How do I break it all down to search the database? I can't search search on the full value. need to search on what the date is, and do a where data like @thisdate?

 

[chiph]

You would usually use the BETWEEN operator in your SQL:

SELECT blah
FROM tblFoo
WHERE DateCol BETWEEN 2004-11-29 AND 2004-11-30

Substitute your variables for the dates (using SqlParameter objects). You can use the .AddDays method on a DateTime object to go forward by a single day, so that you're ensured of getting all records for that day.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[chuckh70]

Great thanks,

Now I just need to figure out the date down to where I can actually use it.

Not sure what you mean by .AddDays? to get the weekly and monthly values?

 

[chiph]

AddDays() is a method on the DateTime struct, and it, uhh, adds days to the value. You can also use to subtract days by passing a negative value.

There are also methods for adding months & years.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[chuckh70]

Ok Thanks,

I got tossed into this position. I knew a little asp so I got tossed into this .net project, and I am just getting more and more confused.

I still can't figure out how to strip the time off the UTC so I can search the database.

Does anyone happen to have an example?

Thanks for all the help

 

[chuckh70]

Maybe I had worded this wrong as well.

The time stored in the database is Unix time. I need to either convert that to normal, or convert todays, and search the db.