Creating SQL views in VBA

Question

I am trying to create a view on a table called petients in my database. The table has five columns. One of them is the column which I want to keep patient admitted date. It data type is datetime so I want to create a query that filters the data in this table based on current date. For example I want create a view that shows only details of petients who have been recorded on the current day.

Here is my code:

CREATE VIEW [dbo].[recent petients]
AS 
    SELECT petient_id, name, age, contact 
    FROM [petients] 
    WHERE [date] = 'date.Today'

I am getting an error saying that failed to convert date to string. Can you help me to solve it, or where is my code wrong?

Please, take care of your punctation for next time. I corrected it for today. Thanks. — Reporter
– Reporter, Commented Feb 22, 2021 at 10:17
You say you want to create a view. Why? This is a trivial query to implement in your application and your application will likely need to query this table in many different ways. So do you intend to create views for every "search" situation? The more objects you add to the database the more work you create maintaining and testing them. I also seems schema issues waiting to be found - but that's a very different question. — SMor
– SMor, Commented Feb 22, 2021 at 14:09

Gordon Linoff · Accepted Answer · 2021-02-22 12:09:43Z

1

Your code looks like SQL Server code. If so, I would recommend:

SELECT petient_id, name, age, contact
FROM [patients] 
WHERE [date] = CONVERT(date, GETDATE());

As a note: This version is much better than DATEDIFF() because it allows the use of an index on patient([date]).

If the "date" column has a time component, you can use:

WHERE CONVERT(date, [date]) = CONVERT(date, GETDATE())

Note that this is also index-safe in SQL Server.

answered Feb 22, 2021 at 12:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SMor Over a year ago

It is sargable but it may not always be a good idea. Certainly easier to understand than that datediff nonsense.

SevC_10 · Accepted Answer · 2021-02-22 10:55:36Z

0

I'm assuming you are using Transact-SQL from Microsoft SQL Server, but you should specify the sql dialect you are using.

Since the datetime field type generally includes also a time, it is better to use the DATEDIFF function: https://learn.microsoft.com/it-it/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver15

In your case, to consider only the record where date=today, the difference in days must be zero:

--SQL QUERY
WHERE DATEDIFF(day, GETDATE(), [date]) = 0

day identifies the element you want to consider the difference. A list of names or abbreviations can be found in the link
GETDATE() returns now datetime
2nd and 3rd arguments are the dates you want to make the difference between

edited Feb 22, 2021 at 10:55

answered Feb 22, 2021 at 10:50

SevC_10

4796 silver badges11 bronze badges

Collectives™ on Stack Overflow

Creating SQL views in VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related