locked
How do you filter Just the current data in SQL Server RRS feed

  • Question

  • How do you filter Just the current data in SQL Server

    does sql server have a basic function that allows you to filter the current data with out the bloody ???

    I found a Mysql command on W3schools that work in the view designer that seems to work as I expect.

    I just want to make sure its proper to use in in mssql

    The Command is = { fn CURDATE() }

    So in my filter  in the view designer I have the following that renders in the SQL text view or the designer

    HAVING        (dbo.Something.SomeField1 <> 9) AND (dbo.Something.SomeField2_DATE = { fn CURDATE() })

    IT works I just want to make sure its right. 

    THanks in advance! 

    Thursday, January 10, 2019 10:23 PM

Answers

  • Hi  PotholesInMyLawn,

     

    >>How do you filter Just the current data in SQL Server?

     

    According to your description, my understanding is that you want to use {fn CURDATE()} to get the current time.  If anything is misunderstood, please tell me.

     

    Based on my test, {fn CURDATE()} works. And {fn CURDATE()} is old stored procedures. I recommend you use the following method to get the current time

    DECLARE @d DATE =GETDATE();

    Your statement can be changed to

     

    DECLARE @d DATE = GETDATE();

    …HAVING   (dbo.Something.SomeField1 <> 9) AND (dbo.Something.SomeField2_DATE = @d);

     

    For more details, please see the blog: https://dba.stackexchange.com/questions/39256/how-does-this-syntax-work-fn-curdate-or-fn-now-etc

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, January 11, 2019 4:45 AM
  • And {fn CURDATE()} is old stored procedures.


    Exactly it's a ODBC Scalar Functions (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, January 11, 2019 7:25 AM
    Answerer
  • You can use this command in WHERE condition in the view do not you?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 13, 2019 5:26 AM
  • Hello, 

    Thank you for the help but in your example you are  passing a parameters... I am trying to do this a sql view where I don't have a parameter ... basicaly when I run this every data it is checking the database for that current day...

    is there a way to do that or should I stay with the = { fn CURDATE() } command ?

    You can use either of the below method

    If column is of datatype date you can use

    HAVING        (dbo.Something.SomeField1 <> 9) 
    AND (dbo.Something.SomeField2_DATE = CAST(GETDATE() as date))

    If column is of type datetime you can use

    HAVING        (dbo.Something.SomeField1 <> 9) 
    AND (dbo.Something.SomeField2_DATE >=  DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
    AND dbo.Something.SomeField2_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1))
    to get current days data


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 13, 2019 6:26 AM
  • Hi,

    SQL server has many of the built-in functions and also if you want you can create your own function (you can find more details here).

    For your example you can use GETDATE() function and also you can apply the proper casting if it is required.

    GETDATE() function will get the current date to test it check below script

    SELECT GETDATE()

    Also you can use it in WHERE clause or HAVING clause to filter the data.

    I hope this is helpful



    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid


    MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
    MCSA: SQL Server 2012/2014
    MCTS: SQL Server Administration/Development
    MyBlog

    Sunday, January 13, 2019 8:31 AM
  • DECLARE @T TABLE ( ID int, CreateDate date ); INSERT INTO @T(ID, CreateDate) VALUES(1, '2019-01-01'), (2, '2019-01-02'), (11, '2019-01-11');

    SELECT * FROM @T WHERE CreateDate = {fn CURRENT_DATE()};



    A Fan of SSIS, SSRS and SSAS


    Friday, January 11, 2019 9:26 PM

All replies

  • Hi  PotholesInMyLawn,

     

    >>How do you filter Just the current data in SQL Server?

     

    According to your description, my understanding is that you want to use {fn CURDATE()} to get the current time.  If anything is misunderstood, please tell me.

     

    Based on my test, {fn CURDATE()} works. And {fn CURDATE()} is old stored procedures. I recommend you use the following method to get the current time

    DECLARE @d DATE =GETDATE();

    Your statement can be changed to

     

    DECLARE @d DATE = GETDATE();

    …HAVING   (dbo.Something.SomeField1 <> 9) AND (dbo.Something.SomeField2_DATE = @d);

     

    For more details, please see the blog: https://dba.stackexchange.com/questions/39256/how-does-this-syntax-work-fn-curdate-or-fn-now-etc

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, January 11, 2019 4:45 AM
  • And {fn CURDATE()} is old stored procedures.


    Exactly it's a ODBC Scalar Functions (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, January 11, 2019 7:25 AM
    Answerer
  • Hello, 

    Thank you for the help but in your example you are  passing a parameters... I am trying to do this a sql view where I don't have a parameter ... basicaly when I run this every data it is checking the database for that current day...

    is there a way to do that or should I stay with the = { fn CURDATE() } command ?

    Friday, January 11, 2019 6:54 PM
  • SELECT {fn CURRENT_DATE()}; -- 2019-01-11

    SELECT CONVERT(date, GETDATE()) -- 2019-01-11


    A Fan of SSIS, SSRS and SSAS


    Friday, January 11, 2019 9:23 PM
  • DECLARE @T TABLE ( ID int, CreateDate date ); INSERT INTO @T(ID, CreateDate) VALUES(1, '2019-01-01'), (2, '2019-01-02'), (11, '2019-01-11');

    SELECT * FROM @T WHERE CreateDate = {fn CURRENT_DATE()};



    A Fan of SSIS, SSRS and SSAS


    Friday, January 11, 2019 9:26 PM
  • You can use this command in WHERE condition in the view do not you?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 13, 2019 5:26 AM
  • Hello, 

    Thank you for the help but in your example you are  passing a parameters... I am trying to do this a sql view where I don't have a parameter ... basicaly when I run this every data it is checking the database for that current day...

    is there a way to do that or should I stay with the = { fn CURDATE() } command ?

    You can use either of the below method

    If column is of datatype date you can use

    HAVING        (dbo.Something.SomeField1 <> 9) 
    AND (dbo.Something.SomeField2_DATE = CAST(GETDATE() as date))

    If column is of type datetime you can use

    HAVING        (dbo.Something.SomeField1 <> 9) 
    AND (dbo.Something.SomeField2_DATE >=  DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
    AND dbo.Something.SomeField2_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1))
    to get current days data


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 13, 2019 6:26 AM
  • Hi,

    SQL server has many of the built-in functions and also if you want you can create your own function (you can find more details here).

    For your example you can use GETDATE() function and also you can apply the proper casting if it is required.

    GETDATE() function will get the current date to test it check below script

    SELECT GETDATE()

    Also you can use it in WHERE clause or HAVING clause to filter the data.

    I hope this is helpful



    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid


    MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
    MCSA: SQL Server 2012/2014
    MCTS: SQL Server Administration/Development
    MyBlog

    Sunday, January 13, 2019 8:31 AM