asked on

ASP Syntax - Date - SQL Statement

When I run this query it works fine:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [ProductId]
      ,[Title]
      ,[Created]
  FROM [DAL.ProductContext].[dbo].[Products]
  WHERE Created LIKE '%2018%'

Open in new window

When I run this, it returns 0 rows. What am I doing wrong? Why is the dash causing problems?
(FYI, there are rows with that date)

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [ProductId]
      ,[Title]
      ,[Created]
  FROM [DAL.ProductContext].[dbo].[Products]
  WHERE Created LIKE '%2018-03-07%'

Open in new window

The date field is Created (datetime, not null)

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Éric Moreau

your field is defined as DateTime? You can use this syntax:

SELECT TOP 1000 [ProductId]
      ,[Title]
      ,[Created]
  FROM [DAL.ProductContext].[dbo].[Products]
  WHERE cast(Created as Date) = '2018-03-07'

Open in new window

bschwarting

ASKER

Thanks guys! Both of those worked great.

Is there a way to input today's date in that line of SQL? (so I don't have to manually put it in every day)

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

WHERE Created >= CONVERT(char(8), GETDATE(), 112) AND Created < CONVERT(char(8), GETDATE() + 1, 112)

Note that normally performing any function on a column can prevent SQL from properly using indexes. MS put in special code so that for certain date/datetime conversions, such as casting to date, SQL will still fully use the indexes. But it's a bad habit, and it can be tricky to determine if/when it applies. If SQL doesn't cover for you, using a function against a data column can severely hurt performance.

Worse, should the column ever be changed to an int -- and sometimes dates are for assorted reasons, some of them good/acceptable -- CASTing the value as date would yield incorrect results along with poor performance.

If the date were changed to a char/varchar -- which is also sometimes done, for no really good reason I can think of -- then CASTing would cause performance issues but would likely still yield correct results.

bschwarting

ASKER

Mad geniuses!!! Great job folks, thank you!!!