November 24, 2015 at 12:48 am
I have the table name of User_Details.It contains the column of User_Last_Login_Date.
User_Last_Login_Date
-------------------------
2015-10-01
2015-11-20
2014-01-12
So i want to calculate the date difference with today date in the form of years, months and days in Select query.
i want to calculate when the user last logged.
for example
Result
-----------
12 days ago
6 months ago
one year ago
I used the below query.But it returns the days difference in minus.Any other best method to calculate...
SELECT
DATEDIFF( mm, '2013-05-26', getdate()) / 12 AS years
, DATEDIFF( mm, '2013-05-26', getdate()) % 12 AS months
, DATEDIFF( dd, DATEADD( mm, DATEDIFF( mm, '2013-05-26',getdate()), '2013-05-26'), getdate())
November 24, 2015 at 2:19 am
jkramprakash (11/24/2015)
I have the table name of User_Details.It contains the column of User_Last_Login_Date.User_Last_Login_Date
-------------------------
2015-10-01
2015-11-20
2014-01-12
So i want to calculate the date difference with today date in the form of years, months and days in Select query.
i want to calculate when the user last logged.
for example
Result
-----------
12 days ago
6 months ago
one year ago
I used the below query.But it returns the days difference in minus.Any other best method to calculate...
SELECT
DATEDIFF( mm, '2013-05-26', getdate()) / 12 AS years
, DATEDIFF( mm, '2013-05-26', getdate()) % 12 AS months
, DATEDIFF( dd, DATEADD( mm, DATEDIFF( mm, '2013-05-26',getdate()), '2013-05-26'), getdate())
SELECT
User_Last_Login_Date,
How_Long_Ago = CASE
WHEN User_Last_Login_Date < x.OneYearAgo THEN 'one year ago'
WHEN User_Last_Login_Date < x.SixMonthsAgo THEN '6 months ago'
WHEN User_Last_Login_Date < x.TwelveDaysAgo THEN '12 days ago'
ELSE NULL END
FROM (VALUES -- sample data - covers range breaks
('20151113'),
('20151112'),
('20150525'),
('20150524'),
('20141125'),
('20141124')
) d (User_Last_Login_Date)
CROSS APPLY (
SELECT
OneYearAgo = DATEADD(YEAR,-1,GETDATE()),
SixMonthsAgo = DATEADD(MONTH,-6,GETDATE()),
TwelveDaysAgo = DATEADD(DAY,-12,GETDATE())
) x
ORDER BY d.User_Last_Login_Date DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 24, 2015 at 2:28 am
Hi
Please clarify, you want to return digits "1 year" or "one year"?
Beside of question above ,small example:
SELECT name,
CAST(DATEDIFF(yy, create_date, GETDATE()) AS NVARCHAR(4)) + ' years, ' +
CAST(DATEDIFF(m, create_date, GETDATE()) AS NVARCHAR(2)) + ' months, ' +
CAST(DATEDIFF(second, create_date, GETDATE()) / 60 / 60 / 24 % 7 AS NVARCHAR(2)) + ' days, ' +
CAST(DATEDIFF(second, create_date, GETDATE()) / 60 / 60 % 24 AS NVARCHAR(2)) + ' hours, ' +
CAST(DATEDIFF(second, create_date, GETDATE()) / 60 % 60 AS NVARCHAR(2)) + ' minutes, ' +
CAST(DATEDIFF(second, create_date, GETDATE()) % 60 AS NVARCHAR(2)) + ' seconds ago.' AS date_period
FROM sys.tables;
Best regards,
Mike
November 24, 2015 at 2:35 am
michal.lisinski (11/24/2015)
HiPlease clarify, you want to return digits "1 year" or "one year"?
Beside of question above ,small example:
SELECT name,
CAST(DATEDIFF(yy, create_date, GETDATE()) AS NVARCHAR(4)) + ' years, ' +
CAST(DATEDIFF(m, create_date, GETDATE()) AS NVARCHAR(2)) + ' months, ' +
CAST(DATEDIFF(second, create_date, GETDATE()) / 60 / 60 / 24 % 7 AS NVARCHAR(2)) + ' days, ' +
CAST(DATEDIFF(second, create_date, GETDATE()) / 60 / 60 % 24 AS NVARCHAR(2)) + ' hours, ' +
CAST(DATEDIFF(second, create_date, GETDATE()) / 60 % 60 AS NVARCHAR(2)) + ' minutes, ' +
CAST(DATEDIFF(second, create_date, GETDATE()) % 60 AS NVARCHAR(2)) + ' seconds ago.' AS date_period
FROM sys.tables;
Best regards,
Mike
It's not as easy as using DATEDIFF, Mike. How many years elapsed between these two dates (none, it's only one day), but look at the return value:
SELECT DATEDIFF(YEAR,'20141231','20150101')
DATEDIFF counts boundaries.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 24, 2015 at 4:19 am
DECLARE @olddate datetime = '20141124 19:50'
SELECT
convert(float,GETDATE()-@olddate) days
, convert(float,GETDATE()-@olddate)/365.25 years
, convert(float,GETDATE()-@olddate)*24*60*60 seconds
, Round (convert(float,GETDATE()-@olddate),0) ROUND_days
, round (convert(float,GETDATE()-@olddate)/365.25, 0) ROUND_years
, round (convert(float,GETDATE()-@olddate)*24*60*60,0) ROUND_seconds
, FLOOR (convert(float,GETDATE()-@olddate)) FLOOR_days
, FLOOR (convert(float,GETDATE()-@olddate)/365.25) FLOOR_years
, FLOOR (convert(float,GETDATE()-@olddate)*24*60*60) FLOOR_seconds
Time (and datetime) is a continues value, so I like to use a 'float'.
I like to define a year as 365.25 days.
For passed time one of the above constructs can be used.
I like to see consistent and informative formats like :
Last use: 2015/05/19 This was 188 days ago.
Most people are capable of converting number of days into weeks/month's and a little less in years. Let us know how your are succeeding.
Ben
November 24, 2015 at 10:21 pm
I checked this query with my data.it is returning the following output.
SELECT last_sign_in_at,
CAST(DATEDIFF(yy, last_sign_in_at, GETDATE()) AS NVARCHAR(4)) + ' years, ' +
CAST(DATEDIFF(m, last_sign_in_at, GETDATE()) AS NVARCHAR(2)) + ' months, ' +
CAST(DATEDIFF(second, last_sign_in_at, GETDATE()) / 60 / 60 / 24 % 7 AS NVARCHAR(2)) + ' days, ' +
CAST(DATEDIFF(second, last_sign_in_at, GETDATE()) / 60 / 60 % 24 AS NVARCHAR(2)) + ' hours, ' +
CAST(DATEDIFF(second, last_sign_in_at, GETDATE()) / 60 % 60 AS NVARCHAR(2)) + ' minutes, ' +
CAST(DATEDIFF(second, last_sign_in_at, GETDATE()) % 60 AS NVARCHAR(2)) + ' seconds ago.' AS date_period
FROM users
Output
----------
last_sign_in_at date_period
2014-09-02 16:22:55 1 years, 14 months, 0 days, 7 hours, 30 minutes, 48 seconds ago.
Actually i want the output is 1 year, 2 months, 23 day ago....
suppose the user last_login_date is 2015-11-23 means.it returns 2 days ago.
Thank you.
November 25, 2015 at 8:08 am
jkramprakash (11/24/2015)
I checked this query with my data.it is returning the following output.SELECT last_sign_in_at,
CAST(DATEDIFF(yy, last_sign_in_at, GETDATE()) AS NVARCHAR(4)) + ' years, ' +
CAST(DATEDIFF(m, last_sign_in_at, GETDATE()) AS NVARCHAR(2)) + ' months, ' +
CAST(DATEDIFF(second, last_sign_in_at, GETDATE()) / 60 / 60 / 24 % 7 AS NVARCHAR(2)) + ' days, ' +
CAST(DATEDIFF(second, last_sign_in_at, GETDATE()) / 60 / 60 % 24 AS NVARCHAR(2)) + ' hours, ' +
CAST(DATEDIFF(second, last_sign_in_at, GETDATE()) / 60 % 60 AS NVARCHAR(2)) + ' minutes, ' +
CAST(DATEDIFF(second, last_sign_in_at, GETDATE()) % 60 AS NVARCHAR(2)) + ' seconds ago.' AS date_period
FROM users
Output
----------
last_sign_in_at date_period
2014-09-02 16:22:55 1 years, 14 months, 0 days, 7 hours, 30 minutes, 48 seconds ago.
Actually i want the output is 1 year, 2 months, 23 day ago....
suppose the user last_login_date is 2015-11-23 means.it returns 2 days ago.
Thank you.
How do you define "One month ago from today"? Is it 28 days ago, or 25th of October?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply