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)
LVL 1
bschwartingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
No telling how dashes will compare with / . or other possible date separators that get added to a date for display purposes only.  Dates are actually stored as int values internally, although SQL uses strings for literal dates.

Here's the correct way to check for a given day against a datetime column:

WHERE Created >= '20180307' AND Created < '20180308'

Format YYYYMMDD day is always interpreted correctly, while YYYY-MM-DD may not be (some instances use YYYY-DD-MM for dates).
1
Éric MoreauSenior .Net ConsultantCommented:
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

0
bschwartingAuthor Commented:
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)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Éric MoreauSenior .Net ConsultantCommented:
WHERE cast(Created as Date) = cast(GetDate() as Date) 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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.
0
bschwartingAuthor Commented:
Mad geniuses!!!  Great job folks, thank you!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.