# Today's date in where clause

### Question

• I have a table called tblDays that has three fields:  trxDay (datetime), trxMonth (datetime) and FiscalYear (nchar(10). The data in this table is between 7/1/2007 and 6/30/2012.  I want to run a query that will get today's date and then return the current month and fiscal year.  I wrote a query which is listed below.  I was expecting it to return one record with 1/30/2012 as the trxDay, 1/1/2012 as the trxMonth and 2012 as the FiscalYear.  This particular record is in the table but the query returns no results.  Does anyone know what I am doing wrong here?

SELECT

TrxDay, TrxMonth,FiscalYear

FROM tblDays

WHERE

(TrxDay = GETDATE

())

Chuck W
Monday, January 30, 2012 3:23 PM

### Answers

• try :

```SELECT
TrxDay, TrxMonth,FiscalYear FROM tblDays
WHERE TrxDay>= Convert(char(8),GETDATE (),112) and TrxDay< Convert(char(8),Dateadd(day,1,GETDATE()),112)
```

Best regards
• Marked as answer by Monday, January 30, 2012 3:29 PM
Monday, January 30, 2012 3:26 PM
• Try in SQL Server 2008:

```SELECT

TrxDay, TrxMonth,FiscalYear

FROM tblDays

WHERE TrxDay = CAST(GETDATE()) AS DATE)

-- in SQL 2005 and less

SELECT

TrxDay, TrxMonth,FiscalYear

FROM tblDays

WHERE TrxDay >= dateadd(day, datediff(day,'19000101',CURRENT_TIMESTAMP),'19000101')
AND TrxDay < dateadd(day, datediff(day,'19000101',CURRENT_TIMESTAMP)+1,'19000101')

```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog
• Proposed as answer by Monday, January 30, 2012 3:32 PM
• Marked as answer by Monday, January 30, 2012 3:39 PM
Monday, January 30, 2012 3:28 PM
• Chuck,

Getdate () function returns the date and time so you need to convert it to date

Try

```SELECT TrxDay, TrxMonth,FiscalYear FROM tblDays WHERE
(convert(date,TrxDay) = convert(date,GETDATE()))
```

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
• Marked as answer by Monday, January 30, 2012 3:34 PM
Monday, January 30, 2012 3:30 PM

### All replies

• try :

```SELECT
TrxDay, TrxMonth,FiscalYear FROM tblDays
WHERE TrxDay>= Convert(char(8),GETDATE (),112) and TrxDay< Convert(char(8),Dateadd(day,1,GETDATE()),112)
```

Best regards
• Marked as answer by Monday, January 30, 2012 3:29 PM
Monday, January 30, 2012 3:26 PM
• Try in SQL Server 2008:

```SELECT

TrxDay, TrxMonth,FiscalYear

FROM tblDays

WHERE TrxDay = CAST(GETDATE()) AS DATE)

-- in SQL 2005 and less

SELECT

TrxDay, TrxMonth,FiscalYear

FROM tblDays

WHERE TrxDay >= dateadd(day, datediff(day,'19000101',CURRENT_TIMESTAMP),'19000101')
AND TrxDay < dateadd(day, datediff(day,'19000101',CURRENT_TIMESTAMP)+1,'19000101')

```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog
• Proposed as answer by Monday, January 30, 2012 3:32 PM
• Marked as answer by Monday, January 30, 2012 3:39 PM
Monday, January 30, 2012 3:28 PM
• Chuck,

Getdate () function returns the date and time so you need to convert it to date

Try

```SELECT TrxDay, TrxMonth,FiscalYear FROM tblDays WHERE
(convert(date,TrxDay) = convert(date,GETDATE()))
```

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
• Marked as answer by Monday, January 30, 2012 3:34 PM
Monday, January 30, 2012 3:30 PM
• Thanks Babii.  That worked.  What does the ,112 do to the query?  I think the char(8) gets the mm/dd/yyyy data and drops the time information correct?  Is the reason why WHERE (TrxDay = GETDATE()) did not work because it is datetime field and not simply a date field?

Chuck

Chuck W
Monday, January 30, 2012 3:32 PM
• Got it.  Thanks.  The query worked.  Chuck
Chuck W
Monday, January 30, 2012 3:34 PM