How to get last 30 days data from today's date in SQL server

Question

1.00/5 (1 vote)

See more:

i have to get record of all rows from last 30 to todays. i have tried this but isn't working

What I have tried:

select date_time from dsr_data where date_time >= CONVERT(Varchar(100), DATEADD(DAY,-30,GETDATE()),103) and date_time <= CONVERT(varchar(100), getdate(),103)

Posted 30-Oct-17 20:54pm

ADI@345

Updated 3-Feb-22 2:09am

Add a Solution

Comments

Santosh kumar Pithani 31-Oct-17 4:36am

You have to convert column "date_time <= CONVERT(varchar(100), getdate(),103)" on bothsides in given condition.

3 solutions

Solution 3

SELECT date_time  FROM dsr_data
    WHERE date_time
               between DATEADD(DAY,-30,GETDATE()) and  GETDATE();

Posted 30-Oct-17 22:29pm

Santosh kumar Pithani

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-10-30T21:05:00

Solution 1

Why are you using CONVERT?
The only possible reason is the same reason it doesn't work: you are storing your dates as NVARCHAR columns instead of DATE, DATETIME, or DATETIME2.

When you compare strings, the comparison is based entirely on the first different character in the two strings: and since you are specifically selecting to format the date in 103 format, which is "dd/MM/yyyy" the comparison will be total junk - the year will be checked last for example, so 31/01/2017 will be before 31/12/1952 because the comparison will be based on the first digit on the month, and '0' is less than '1'

Change your database. Always store data in appropriate datatypes: that means integer values in INT columns, date information in DATE, DATETIME, or DATETIME2 columns, and so on. If you don't, this is just the first of many nasty, nasty problems waiting to bite you down the line...

Posted 30-Oct-17 21:05pm

OriginalGriff

Comments

ADI@345 31-Oct-17 3:14am

now i change nvarchar to datetime , and using this query but didn't work.

select date_time from dsr_data where date_time >= DATEADD(DAY,-30,GETDATE()) and
date_time <= getdate()

OriginalGriff 31-Oct-17 3:30am

I would use BETWEEN, but assuming your data in the table is correct, your query should work. - it does when I run it against my data (with the table and column names changed).
So start by looking at your data: Assuming "didn't work" means it returned no rows, comment out the "AND date_time ..." part so it's returning everything for thirty days again, and see what you get.

ADI@345 31-Oct-17 4:13am

here on datetime data save as YYYY-MM-DD
but on getdate() format i get as YYYY-DD-MM

CHill60 31-Oct-17 4:43am

It doesn't matter how the date displays - when it is stored as a datetime it is just that - a date + time. So using between will still work

ADI@345 31-Oct-17 4:13am

i thought this is the problem ...

OriginalGriff 31-Oct-17 4:39am

That's why you can't store dates as NVARCHAR - when SQL has to convert them back, it will guess what format it is, and most likely get it wrong, in some cases if not all.
So when you changed your NVARCHAR column to DATETIME, SQL had to try and work out what each date was.
Start by looking at your data - until you CONVERT it it has no formatting info at all, it's stored as "ticks since a specific point in time" rather than years, months, and days - and get that right (I'd use C# or VB to read the row, make sure it's right, and UPDATE sql where necessary)

But looking at your database definition from your comments below, there are other problems here.
Don't store TIME info separately from DATE, and always store DATETIME info in UCT, not local time - that way when the local "summer" or "daylight saving" time comes in or out, your DB doesn't get filled with inconsistent data.
Don't store "client type" as a string, create a second table, and use a foreign key to index it.
100 chars is not a good idea for Mobile numbers, or email - in one case it's too big, in the other it's too small!
You should have an ID column - IDENTITY is fine - to give you an absolute reference to the column when you need to change it. At the moment, if a client changes his mobile, how do you know which row to UPDATE?

ADI@345 31-Oct-17 4:44am

thanks sir for your suggestion , i will do this and i solved this problem ..my code now work fine..

ADI@345 31-Oct-17 4:47am

but is there any way to remove time from datetime.

OriginalGriff 31-Oct-17 4:58am

If you need to, then yes - casting it to a DATE will do that.
But adding a time to a date is is just a PITA when you need to use the time value, particularly when you have the time stored as a NVARCHAR!

ADI@345 31-Oct-17 6:35am

how to get data between two date :

void gvbind()
{

string Query = "SELECT * FROM [dsr_data] WHERE date_time between date_time '"+TextBox1.Text+"' and date_time '"+TextBox4.Text+"'";
DataTable dtAdmin = new DataTable();
SqlDataAdapter da;
da = new SqlDataAdapter(Query, cnn);
da.Fill(dtAdmin);
if (dtAdmin.Rows.Count > 0)
{
GVmydsr.DataSource = dtAdmin;
GVmydsr.DataBind();

}
else
{
GVmydsr.DataSource = null;
GVmydsr.DataBind();
}
}

ADI@345 31-Oct-17 6:37am

i also solve the above that i ask , leave it thanks..

OriginalGriff 31-Oct-17 6:41am

NO!
Never do that - it's extremely dangerous.
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

DROP TABLE MyTable;

A perfectly valid "delete the table" command

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Atlapure Ambrish · Accepted Answer · 2017-10-30T21:07:00

Solution 2

Use below query.

SELECT date_time  FROM  dsr_data
WHERE date_time  >= DATEADD(day,-30, getdate()) 
and   date_time  <= getdate()

Posted 30-Oct-17 21:07pm

Atlapure Ambrish

Comments

ADI@345 31-Oct-17 3:20am

i am using the same code but didn't work

Atlapure Ambrish 31-Oct-17 3:42am

This should work, I have checked it and is working for me. What is the datatype of date_time column?

ADI@345 31-Oct-17 3:52am

CREATE TABLE [dbo].[dsr_data](
[client_type] [varchar](100) NULL,
[date_time] datetime NULL,
[tme] [varchar](100) NULL,
[area] [varchar](100) NULL,
[company_name] [varchar](100) NULL,
[client_name] [varchar](100) NULL,
[mobile] [varchar](100) NULL,
[email] [varchar](100) NULL,
[address_name] [varchar](100) NULL,
[landmark] [varchar](100) NULL,
[curr_presence_appoint] [varchar](100) NULL

) ON [PRIMARY]