Reading date string by current month, year

[zenweezil]

I have a timestamp field in my database and I want to write an SQL command that brings in only the records made in the current month and year regardless of the day or time - any ideas on the easiest way to do this?

For example - my database has:
entryName dateSent
ENTRY 1 - 10/18/2005 8:31:55 AM
ENTRY 2 - 9/22/2005 5:55:12 PM
ENTRY 3 - 10/12/2004 2:36:15 AM

So I want an SQL string that results in a recordset with just ENTRY 1 since it is the only one in October of 2005.

I know of several long ways around to getting this, but am hoping I am missing an easy way to incoporate directly into the SQL string.

[BaldEagle]

I have a timestamp field in my database and I want to write an SQL command that brings in only the records made in the current month and year regardless of the day or time - any ideas on the easiest way to do this?

For example - my database has:
entryName dateSent
ENTRY 1 - 10/18/2005 8:31:55 AM
ENTRY 2 - 9/22/2005 5:55:12 PM
ENTRY 3 - 10/12/2004 2:36:15 AM

So I want an SQL string that results in a recordset with just ENTRY 1 since it is the only one in October of 2005.

I know of several long ways around to getting this, but am hoping I am missing an easy way to incoporate directly into the SQL string.

You can use the LIKE operator in your SQL statement:


// format the date to get only the short date part
Session("AB_Date") = CStr(FormatDateTime(Now,2))
// use LIKE to extract the correct dates regardless of times
strSQL = "SELECT * FROM tbl_ABook WHERE A_Date LIKE '%" & Session("AB_Date") & "%'"


I used a session variable in this example, as I extracted it from an appointment book program I have, but you can use it however you want.

BaldEagle

[zenweezil]

I am unable to get LIKE to work.

Here are some examples of what I tried:
SELECT count(*) as appointmentcount FROM dbo.T_Forms WHERE CONVERT(varchar, dateSent) LIKE '%10/18/2005%'

SELECT count(*) as appointmentcount FROM dbo.T_Forms WHERE dateSent LIKE '%10/18/2005%'

[BaldEagle]

Are you saying that rs(0) = 0?

BaldEagle

[Roelf]

please do not convert to string and then use like.

Why nog use:
select * from dbo.T_Forms where month(dateSent)=10 and year(dateSent)=2005

try to find the command for current date in the sql documentation, it might be like
select * from dbo.T_Forms where month(dateSent)=month(getdate()) and year(dateSent)=year(getdate())

[zenweezil]

This is exaclty the type of direct computation I was looking for:

select * from dbo.T_Forms where month(dateSent)=month(getdate()) and year(dateSent)=year(getdate())

Thank You. :thumbsup: