SQL Query Sorting

Question

I have a table which I want to run a query against to pull out data to a csv file. The data needs to be sorted by username and I only want output of people created within the last seven days.

So far I can pull out the data that I need, but can't quite figure how to achieve the piece surrounding only the last seven days. My query looks like this at present;

SELECT
  user_name,
  created_date
FROM DATABASENAME.dbo.tbl_user
WHERE user_name LIKE 'guest-%'

A line of the resulting output (once in the csv) looks like;

guest-12343,2016-09-20 19:57:50.347

Can anyone recommend how I might be able to adapt the statement to be more selective on with the data in the created_date column?

You need to add condition that your date is between today and today - 6 days. — PM 77-1
– PM 77-1, Commented Sep 22, 2016 at 22:13

Mohammad Hanafy · Accepted Answer · 2016-09-22 22:18:54Z

1

For getting data within 7 days add this in your where clause

AND created_date >= DATEADD(day, -6, convert(date, GETDATE()))

For Sorting with user name add this @end

ORDER BY user_name

So final Query will be :

SELECT user_name, created_date FROM DATABASENAME.dbo.tbl_user WHERE user_name like 'guest-%' created_date >= DATEADD(day, -7, convert(date, GETDATE())) ORDER BY user_name

answered Sep 22, 2016 at 22:18

Mohammad Hanafy

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

grayimp · Accepted Answer · 2016-09-22 22:21:48Z

You can use created_date in your WHERE clause. Now the question is how do you define "last seven days." If you want 7 * 24 hours before now, it would be

SELECT user_name, created_date 
FROM DATABASENAME.dbo.tbl_user 
WHERE user_name like 'guest-%' 
  AND created_date >= GETDATE() - 7

If you want your 7 day period to be 6 full days plus today, it would be:

SELECT user_name, created_date 
FROM DATABASENAME.dbo.tbl_user 
WHERE user_name like 'guest-%' 
  AND created_date >= CONVERT(DATE, GETDATE() - 6)

Or if you want your 7 day period to be 7 full days not including today, it would be:

SELECT user_name, created_date 
FROM DATABASENAME.dbo.tbl_user 
WHERE user_name like 'guest-%' 
  AND created_date >= CONVERT(DATE, GETDATE() - 7)
  AND created_date < CONVERT(DATE, GETDATE())

This is assuming created_date is storing dates in the server's timezone. If it has UTC dates, change GETDATE() to GETUTCDATE().

Matt · Accepted Answer · 2016-09-22 22:20:27Z

0

SELECT user_name, created_date 
FROM DATABASENAME.dbo.tbl_user 
WHERE
    user_name like 'guest-%'
    AND Created_Date >= CAST(GETDATE() - 7 AS DATE)
ORDER BY user_name

Assuming you want from 12:01 AM 7 days ago and that Created_Date can never be greater than Now [Getdate()]

answered Sep 22, 2016 at 22:20

Matt

14.4k2 gold badges19 silver badges29 bronze badges

Collectives™ on Stack Overflow

SQL Query Sorting

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related