How to Get Current/Todays Date Data in SQL Server

how to get current/Todays date data in sql server

The correct answer will depend of the exact type of your datecolumn. Assuming it is of type Date :

select * from tbl_name 
where datecolumn = cast(getdate() as Date)

If it is DateTime:

select * from tbl_name 
where cast(datecolumn as Date) = cast(getdate() as Date)

SQL query to show data by today(current) date?

Just compare the date part

   Select *from Transactions
        WHERE 
    CONVERT(char(10), transaction_date  ,126)=CONVERT(char(10), getdate(),126);

How to select SQL Server data based on today's date and time?

You can use convert function like this

where convert(Date,date_time)= CONVERT(Date,GETDATE())

Get todays date a year ago

Try using:

CONVERT(DateTime, DATEDIFF(DAY, 0, DATEADD(year, -1, GETDATE())))

Here is a query to see this in action:

SELECT
    GETDATE() AS today,
    DATEADD(year, -1, GETDATE()) AS today_last_year,  -- what you already have
    CONVERT(DateTime, DATEDIFF(DAY, 0, DATEADD(year, -1, GETDATE()))) AS
        today_last_year_midnight;

This returned (as of the time of writing this answer):

Getting the current date in SQL Server?

SELECT CAST(GETDATE() AS DATE)

Returns the current date with the time part removed.

DATETIMEs are not "stored in the following format". They are stored in a binary format.

SELECT CAST(GETDATE() AS BINARY(8))

The display format in the question is independent of storage.

Formatting into a particular display format should be done by your application.

SQL display | select data from today date | current day only

Look at this example:

declare @visitTime datetime  ='2014-10-16 23:59:59.000'
select GETDATE() GETDATE, @visitTime visitTime, GETDATE() - 1 [GETDATE-1]

GETDATE                 visitTime               GETDATE-1
2014-10-17 00:02:18.980 2014-10-16 23:59:59.000 2014-10-16 00:02:18.980

You'll see that the visittime date clearly falls in the range you specified as the lower bound (the -1) subtracts a whole day and not just the time part.

You could use this instead:

-- using GETDATE() for the upper bound misses visitTime that are 
-- on the current day, but at a later time than now.
WHERE visitTime < DateAdd(Day, DateDiff(Day, 0, GetDate())+1, 0)
AND   visitTime >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

or if you're on SQL Server 2008+ that has adatedata type, this:

WHERE CAST(visitTime AS DATE) = CAST(GETDATE() AS DATE)

Note thatGETDATE()is T-SQL specific, the ANSI equivalent isCURRENT_TIMESTAMP

sql server query current date from database

If you are looking for the current date:

WHERE TransactionDate = cast(getdate() as date)

Or if you prefer ANSI standards:

WHERE TransactionDate = cast(CURRENT_TIMESTAMP as date)

SQL where datetime column equals today's date?

Looks like you're using SQL Server, in which case GETDATE() or current_timestamp may help you. But you will have to ensure that the format of the date with which you are comparing the system dates matches (timezone, granularity etc.)

e.g.

where convert(varchar(10), submission_date, 102) 
    = convert(varchar(10), getdate(), 102)

SQL query that displays the current date and count of days between two specific dates

updated to include 5 days from previous month.

Is this what you are looking for the second part?

Edit: Modifying the query. I guess this should give you what you need for both the parts.
you can try changing the dates in set @date.

Please note that the -4 instead of -5 is done intentionally as you said the financial month starts 5 days earlier. For March, 31 - 5 would give 26, but it should start on 27 right? so that on 29th the no. of days should be 3 including 27 and 29. Anyways, the query should be self explanatory, might just need to change the number depending on your requirement.

declare @date datetime
--set @date = getdate()
set @date = '2020-02-14'

SELECT format(@date,'yyyy-MM-dd') as date,
case WHEN @date < dateadd(day,-4,EOMONTH(@date)) THEN format(EOMONTH(@date),'yyyy-MM')
     ELSE format(EOMONTH(@date, 1),'yyyy-MM') END
     AS FinancialMonth,
     CASE WHEN @date < dateadd(day,-4,EOMONTH(@date)) THEN DATEDIFF(day,dateadd(day,-5,EOMONTH(@date,-1)), @date)
     ELSE DATEDIFF(day, dateadd(day,-5,EOMONTH(@date)), @date) END
     AS CountDays

---

Related Topics