Roman78 Roman78 - 1 year ago 50
SQL Question

SQL query convert today date into yyyymmdd

We having and SQL 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 field 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 whit Datepart and getdate. But somehow it wont 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 a SQL 2000 Server.

Answer Source

Try this query,

select data from table where CAST(UPD_DAT AS NVARCHAR(10)) = CONVERT(NVARCHAR(10), GetDate(),112)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download