complex select statement help...

[bfsog]

Hey.

I need help with a select statement. In my table "users" I have a last_login field, which type is date

I need to select all users who have logged in in the current month.

For instance, if user bob and user santa last_login value is between the 1st of a month and the last day of a month, select them?

I guess I will have to use a function to get the days in a particular month, but because the month number is in a field, it will be hard to extract.

Thanks for any help.

 

[jmcilhinney]

The following SQL syntax is correct for MS Access. You may need to alter it a lttle for other databases but the principles are the same.

[color=Green]'Get the first day of the current month.[/color]
[color=Blue]Dim[/color] startDate [color=Blue]As Date[/color] = [color=Blue]Date[/color].Today.AddDays(1 - [color=Blue]Date[/color].Today.Day)

[color=Green]'Get the last day of the current month.[color=Blue]
[/color][/color][color=Blue]Dim[/color] endDate [color=Blue]As Date[/color] = [color=Blue]Date[/color].Today.AddDays([color=Blue]Date[/color].DaysInMonth([color=Blue]Date[/color].Today.Year, [color=Blue]Date[/color].Today.Month) - [color=Blue]Date[/color].Today.Day)[color=Blue]

Dim[/color] SQL [color=Blue]As String[/color] = [color=Blue]String[/color].Format("SELECT * FROM users WHERE last_login BETWEEN DateValue('{0}') AND DateValue('{1}')", startDate, endDate)

You can adapt this for any date simply by substituting the desired date for Date.Today.

 

[bfsog]

Sorry to bring up an old thread, but I have this code:

			    startDate = Date.Today.AddDays(1 - Date.Today.Day)
			    MessageBox.Show(startDate, "startdate in month block is:")
				test = startDate.ToShortDateString
			    'Get the last day of the current month.
			    endDate = Date.Today.AddDays(Date.DaysInMonth(Date.Today.Year, Date.Today.Month) - Date.Today.Day)
			    MessageBox.Show(endDate, "enddate in month block is:")
				test2 = endDate.ToShortDateString
			    Me.OleDbSelectCommand1.CommandText = "SELECT * FROM users WHERE (last_login BETWEEN '" + test + "' AND '" + test2 + "')"

It returns rows between 01/06/2005 and 30/05/2005 but also returns a row with last_login as 27/05/2005

startDate and endDate when messages boxed out are format dd/mm/yyyy

Is it because my last_login columns type is date?
If not any recommend what to do?

TIA

 

[jmcilhinney]

What database are you using? If it is Access I suggest you use the DateValue() function without converting your dates to strings as I originally posted. If it is something else, I'm not sure but I think you should put "#" symbols around your dates rather than single quotes.

 

[bfsog]

Thanks again, I am using an Access database.

Can I just clarify some things?

1: In my table "users" my column last_login is a date type. - is this okay?

2: When I use the datevalue, what goes in the braces?

DateValue('{0}') AND DateValue('{1}')

I think I would put the variables that hold the dates but I am not sure?

Thanks in advance

 

[jmcilhinney]

1. Yes that is definitely the correct type to use.
2. You'll notice that in my post I have used String.Format(). This substitutes the other arguments, in this case startDate and endDate, for the placeholders in braces. You could just use string concatenation, in which case you would use the actual variables, but String.Format() is more efficient and generally looks more understandable in code.

 

[bfsog]

I think I understand, the datevalue part is basiclly putting startDate where the 0 is and endDate where the 1 is?

 

[jmcilhinney]

That's not correct.

The String.Format() function takes a string as its first argument. In that string you put placeholders that are integers in braces. A string representation of the second argument to String.Format() is substituted wherever the placeholder "{0}" is found, a string representation of the third argument is substituted wherever the placeholder "{1}" is found and so on. The resulting string is then returned by the function.

DateValue() is an Access function. It is used by the database engine to convert a string representation of a date (and optionally a time) into an actual date structure for comparison against other date structures stored in the database.

Put this line of code in you project and you'll see exactly what SQL statement you are executing:

MessageBox.Show(String.Format("SELECT * FROM users WHERE last_login BETWEEN DateValue('{0}') AND DateValue('{1}')", startDate, endDate))

 

[bfsog]

Ah, I message boxed out that, and the messagebox includes the time, however in my column, it is only the date, such as
01/06/2005

How do i get that format in vb.net? Right now I do

		'Get the first day of the current month.
		startDate = Date.Today.AddDays(1 - Date.Today.Day)
		'Get the last day of the current month.
		endDate = Date.Today.AddDays(Date.DaysInMonth(Date.Today.Year, Date.Today.Month) - Date.Today.Day)

? TIA

 

[jmcilhinney]

If you want just the date from a Date or DateTime object in VB.NET you use the Date property of each, i.e. Date.Date or DateTime.Date, but the DateValue function extracts just the date anyway. There is also a TimeValue function.