We have a SQL Server 2000 database for our business. Now I'm trying to make an easy to use and readable output for our staff. The SQL query I made works fine, but as said I want to make it easy to use. So that our employee only has to press an update button and he or she receives the actual numbers.
The database uses on every record an UPD_DAT column containing to actual date that record is made. But in a YYYYMMDD format. So for today it is 20170512. So far so good. Now I want to use today as query. So I googled and tried something with Datepart and getdate. But somehow it won't work.
So I get something like that:
SELECT data
FROM table
WHERE UPD_DAT = (DATEPART("YYYY", (GETDATE())) * 100 +
DATEPART("MM", MONTH(GETDATE()))) * 100 +
DATEPART("DD", (GETDATE()))
I started in January whit this query and thought that it's working. Today I continued whit it and noticed some problems. The result I get is in January not in may, I get 20170112 instead of 20170512.
As mentioned it is SQL Server 2000.
Try this query,
select data from table where CAST(UPD_DAT AS NVARCHAR(10)) = CONVERT(NVARCHAR(10), GetDate(),112)