Need to calculate date difference of today date vs converted date field
up vote
1
down vote
favorite
I have a table with a column that was previously converted using
(CONVERT(VARCHAR(19), GETDATE(), 112)
With the getdate being the date when the field was inserted. Now I have to compare the current date against the date the field was inserted.
The issue I'm facing is that when the date field is from last month, say 20131004, I calculate date difference by (CONVERT(VARCHAR(19), GETDATE(), 112) - 20131004, the result is 200. Obviously this is wrong...
Could you please suggest me how I could calculate the true date difference?
sql sql-server-2012
asked Dec 3 '13 at 23:24
user2669043
32211
add a comment |
up vote
1
down vote
favorite
I have a table with a column that was previously converted using
(CONVERT(VARCHAR(19), GETDATE(), 112)
With the getdate being the date when the field was inserted. Now I have to compare the current date against the date the field was inserted.
The issue I'm facing is that when the date field is from last month, say 20131004, I calculate date difference by (CONVERT(VARCHAR(19), GETDATE(), 112) - 20131004, the result is 200. Obviously this is wrong...
Could you please suggest me how I could calculate the true date difference?
sql sql-server-2012
asked Dec 3 '13 at 23:24
user2669043
32211
3
Why don't you change the data type to the right one (DATE) instead of dealing with conversions to and from a string? Also stop using lazy shorthand likeDATE1 - DATE2(it doesn't work with the data types you should be using since SQL Server 2008). UseDATEDIFF(DAY, column, GETDATE()). Assuming all of your data is "good" (any bad data can get in there when you use the wrong data type), this should work without any conversions.
– Aaron Bertrand
Dec 3 '13 at 23:27
thanks but, the watch is that the table wasn't created by me and I donot have access to how the date is being inserted. I need to deal with what I have...
– user2669043
Dec 4 '13 at 3:21
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have a table with a column that was previously converted using
(CONVERT(VARCHAR(19), GETDATE(), 112)
With the getdate being the date when the field was inserted. Now I have to compare the current date against the date the field was inserted.
The issue I'm facing is that when the date field is from last month, say 20131004, I calculate date difference by (CONVERT(VARCHAR(19), GETDATE(), 112) - 20131004, the result is 200. Obviously this is wrong...
Could you please suggest me how I could calculate the true date difference?
sql sql-server-2012
asked Dec 3 '13 at 23:24
user2669043
32211
I have a table with a column that was previously converted using
(CONVERT(VARCHAR(19), GETDATE(), 112)
With the getdate being the date when the field was inserted. Now I have to compare the current date against the date the field was inserted.
The issue I'm facing is that when the date field is from last month, say 20131004, I calculate date difference by (CONVERT(VARCHAR(19), GETDATE(), 112) - 20131004, the result is 200. Obviously this is wrong...
Could you please suggest me how I could calculate the true date difference?
sql sql-server-2012
sql sql-server-2012
asked Dec 3 '13 at 23:24
user2669043
32211
asked Dec 3 '13 at 23:24
user2669043
32211
asked Dec 3 '13 at 23:24
user2669043
32211
asked Dec 3 '13 at 23:24
user2669043
32211
asked Dec 3 '13 at 23:24
user2669043
32211
32211
3
Why don't you change the data type to the right one (DATE) instead of dealing with conversions to and from a string? Also stop using lazy shorthand likeDATE1 - DATE2(it doesn't work with the data types you should be using since SQL Server 2008). UseDATEDIFF(DAY, column, GETDATE()). Assuming all of your data is "good" (any bad data can get in there when you use the wrong data type), this should work without any conversions.
– Aaron Bertrand
Dec 3 '13 at 23:27
thanks but, the watch is that the table wasn't created by me and I donot have access to how the date is being inserted. I need to deal with what I have...
– user2669043
Dec 4 '13 at 3:21
add a comment |
3
Why don't you change the data type to the right one (DATE) instead of dealing with conversions to and from a string? Also stop using lazy shorthand likeDATE1 - DATE2(it doesn't work with the data types you should be using since SQL Server 2008). UseDATEDIFF(DAY, column, GETDATE()). Assuming all of your data is "good" (any bad data can get in there when you use the wrong data type), this should work without any conversions.
– Aaron Bertrand
Dec 3 '13 at 23:27
thanks but, the watch is that the table wasn't created by me and I donot have access to how the date is being inserted. I need to deal with what I have...
– user2669043
Dec 4 '13 at 3:21
3
3
Why don't you change the data type to the right one (
DATE) instead of dealing with conversions to and from a string? Also stop using lazy shorthand like DATE1 - DATE2 (it doesn't work with the data types you should be using since SQL Server 2008). Use DATEDIFF(DAY, column, GETDATE()). Assuming all of your data is "good" (any bad data can get in there when you use the wrong data type), this should work without any conversions.– Aaron Bertrand
Dec 3 '13 at 23:27
Why don't you change the data type to the right one (
DATE) instead of dealing with conversions to and from a string? Also stop using lazy shorthand like DATE1 - DATE2 (it doesn't work with the data types you should be using since SQL Server 2008). Use DATEDIFF(DAY, column, GETDATE()). Assuming all of your data is "good" (any bad data can get in there when you use the wrong data type), this should work without any conversions.– Aaron Bertrand
Dec 3 '13 at 23:27
thanks but, the watch is that the table wasn't created by me and I donot have access to how the date is being inserted. I need to deal with what I have...
– user2669043
Dec 4 '13 at 3:21
thanks but, the watch is that the table wasn't created by me and I donot have access to how the date is being inserted. I need to deal with what I have...
– user2669043
Dec 4 '13 at 3:21
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
I agree with @Aaron that you should store a date field in a date field, not a text field.
If for some reason you do want to store it in a text field then the easiest way to calculate the number of days is to convert it back to a date field and then compare it:
SELECT DATEDIFF(DAY, CAST(column_name as DATETIME), GETDATE()) FROM dbo.table
This will throw an error if the value in the column cannot be converted to a date. You'll also need to make sure that the date is formatted correctly for your database. Assuming you use the format 112 you should be ok, but if you have the value 04/12/2013 in the column is that the 4th December 2013 or the 12th April 2013? It depends on how your database is configured.
But anyway, if you always insert dates in that field then you're nuts not making it a date field.
If you need to display the date somewhere then convert it on the way out.
answered Dec 3 '13 at 23:46
Greg
2,40432042
add a comment |
up vote
3
down vote
You should be using the
DATEorDATETIMEdata type for that column. Why on earth would you ever store a date as a string? Do you know how much you lose by doing so? Validation, for one - aVARCHAR(19)column will accept20131004 12:34 PMbut will also accept nonsense values likeI am not a date!.
If the data is actually good, you can simply do this instead of lazy shorthand and without any explicit conversions:
SELECT DATEDIFF(DAY, column_name, GETDATE()) FROM dbo.table;
If you get an error message with this, then you have bad data. You can identify it like this:
SELECT column_name FROM dbo.table WHERE ISDATE(column_name) = 0;
Please read:
Bad habits to kick : choosing the wrong data type
Bad habits to kick : mis-handling date / range queries
Bad Habits to Kick : Using shorthand with date/time operations
answered Dec 3 '13 at 23:31
Aaron Bertrand
205k27357401
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
I agree with @Aaron that you should store a date field in a date field, not a text field.
If for some reason you do want to store it in a text field then the easiest way to calculate the number of days is to convert it back to a date field and then compare it:
SELECT DATEDIFF(DAY, CAST(column_name as DATETIME), GETDATE()) FROM dbo.table
This will throw an error if the value in the column cannot be converted to a date. You'll also need to make sure that the date is formatted correctly for your database. Assuming you use the format 112 you should be ok, but if you have the value 04/12/2013 in the column is that the 4th December 2013 or the 12th April 2013? It depends on how your database is configured.
But anyway, if you always insert dates in that field then you're nuts not making it a date field.
If you need to display the date somewhere then convert it on the way out.
answered Dec 3 '13 at 23:46
Greg
2,40432042
add a comment |
up vote
1
down vote
accepted
I agree with @Aaron that you should store a date field in a date field, not a text field.
If for some reason you do want to store it in a text field then the easiest way to calculate the number of days is to convert it back to a date field and then compare it:
SELECT DATEDIFF(DAY, CAST(column_name as DATETIME), GETDATE()) FROM dbo.table
This will throw an error if the value in the column cannot be converted to a date. You'll also need to make sure that the date is formatted correctly for your database. Assuming you use the format 112 you should be ok, but if you have the value 04/12/2013 in the column is that the 4th December 2013 or the 12th April 2013? It depends on how your database is configured.
But anyway, if you always insert dates in that field then you're nuts not making it a date field.
If you need to display the date somewhere then convert it on the way out.
answered Dec 3 '13 at 23:46
Greg
2,40432042
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
I agree with @Aaron that you should store a date field in a date field, not a text field.
If for some reason you do want to store it in a text field then the easiest way to calculate the number of days is to convert it back to a date field and then compare it:
SELECT DATEDIFF(DAY, CAST(column_name as DATETIME), GETDATE()) FROM dbo.table
This will throw an error if the value in the column cannot be converted to a date. You'll also need to make sure that the date is formatted correctly for your database. Assuming you use the format 112 you should be ok, but if you have the value 04/12/2013 in the column is that the 4th December 2013 or the 12th April 2013? It depends on how your database is configured.
But anyway, if you always insert dates in that field then you're nuts not making it a date field.
If you need to display the date somewhere then convert it on the way out.
answered Dec 3 '13 at 23:46
Greg
2,40432042
I agree with @Aaron that you should store a date field in a date field, not a text field.
If for some reason you do want to store it in a text field then the easiest way to calculate the number of days is to convert it back to a date field and then compare it:
SELECT DATEDIFF(DAY, CAST(column_name as DATETIME), GETDATE()) FROM dbo.table
This will throw an error if the value in the column cannot be converted to a date. You'll also need to make sure that the date is formatted correctly for your database. Assuming you use the format 112 you should be ok, but if you have the value 04/12/2013 in the column is that the 4th December 2013 or the 12th April 2013? It depends on how your database is configured.
But anyway, if you always insert dates in that field then you're nuts not making it a date field.
If you need to display the date somewhere then convert it on the way out.
answered Dec 3 '13 at 23:46
Greg
2,40432042
answered Dec 3 '13 at 23:46
Greg
2,40432042
answered Dec 3 '13 at 23:46
Greg
2,40432042
answered Dec 3 '13 at 23:46
Greg
2,40432042
2,40432042
add a comment |
add a comment |
up vote
3
down vote
You should be using the
DATEorDATETIMEdata type for that column. Why on earth would you ever store a date as a string? Do you know how much you lose by doing so? Validation, for one - aVARCHAR(19)column will accept20131004 12:34 PMbut will also accept nonsense values likeI am not a date!.
If the data is actually good, you can simply do this instead of lazy shorthand and without any explicit conversions:
SELECT DATEDIFF(DAY, column_name, GETDATE()) FROM dbo.table;
If you get an error message with this, then you have bad data. You can identify it like this:
SELECT column_name FROM dbo.table WHERE ISDATE(column_name) = 0;
Please read:
Bad habits to kick : choosing the wrong data type
Bad habits to kick : mis-handling date / range queries
Bad Habits to Kick : Using shorthand with date/time operations
answered Dec 3 '13 at 23:31
Aaron Bertrand
205k27357401
add a comment |
up vote
3
down vote
You should be using the
DATEorDATETIMEdata type for that column. Why on earth would you ever store a date as a string? Do you know how much you lose by doing so? Validation, for one - aVARCHAR(19)column will accept20131004 12:34 PMbut will also accept nonsense values likeI am not a date!.
If the data is actually good, you can simply do this instead of lazy shorthand and without any explicit conversions:
SELECT DATEDIFF(DAY, column_name, GETDATE()) FROM dbo.table;
If you get an error message with this, then you have bad data. You can identify it like this:
SELECT column_name FROM dbo.table WHERE ISDATE(column_name) = 0;
Please read:
Bad habits to kick : choosing the wrong data type
Bad habits to kick : mis-handling date / range queries
Bad Habits to Kick : Using shorthand with date/time operations
answered Dec 3 '13 at 23:31
Aaron Bertrand
205k27357401
add a comment |
up vote
3
down vote
up vote
3
down vote
You should be using the
DATEorDATETIMEdata type for that column. Why on earth would you ever store a date as a string? Do you know how much you lose by doing so? Validation, for one - aVARCHAR(19)column will accept20131004 12:34 PMbut will also accept nonsense values likeI am not a date!.
If the data is actually good, you can simply do this instead of lazy shorthand and without any explicit conversions:
SELECT DATEDIFF(DAY, column_name, GETDATE()) FROM dbo.table;
If you get an error message with this, then you have bad data. You can identify it like this:
SELECT column_name FROM dbo.table WHERE ISDATE(column_name) = 0;
Please read:
Bad habits to kick : choosing the wrong data type
Bad habits to kick : mis-handling date / range queries
Bad Habits to Kick : Using shorthand with date/time operations
answered Dec 3 '13 at 23:31
Aaron Bertrand
205k27357401
You should be using the
DATEorDATETIMEdata type for that column. Why on earth would you ever store a date as a string? Do you know how much you lose by doing so? Validation, for one - aVARCHAR(19)column will accept20131004 12:34 PMbut will also accept nonsense values likeI am not a date!.
If the data is actually good, you can simply do this instead of lazy shorthand and without any explicit conversions:
SELECT DATEDIFF(DAY, column_name, GETDATE()) FROM dbo.table;
If you get an error message with this, then you have bad data. You can identify it like this:
SELECT column_name FROM dbo.table WHERE ISDATE(column_name) = 0;
Please read:
Bad habits to kick : choosing the wrong data type
Bad habits to kick : mis-handling date / range queries
Bad Habits to Kick : Using shorthand with date/time operations
answered Dec 3 '13 at 23:31
Aaron Bertrand
205k27357401
edited Nov 10 at 16:04
answered Dec 3 '13 at 23:31
Aaron Bertrand
205k27357401
answered Dec 3 '13 at 23:31
Aaron Bertrand
205k27357401
answered Dec 3 '13 at 23:31
Aaron Bertrand
205k27357401
205k27357401
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f20363797%2fneed-to-calculate-date-difference-of-today-date-vs-converted-date-field%23new-answer', 'question_page');
}
);
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
3
Why don't you change the data type to the right one (
DATE) instead of dealing with conversions to and from a string? Also stop using lazy shorthand likeDATE1 - DATE2(it doesn't work with the data types you should be using since SQL Server 2008). UseDATEDIFF(DAY, column, GETDATE()). Assuming all of your data is "good" (any bad data can get in there when you use the wrong data type), this should work without any conversions.– Aaron Bertrand
Dec 3 '13 at 23:27
thanks but, the watch is that the table wasn't created by me and I donot have access to how the date is being inserted. I need to deal with what I have...
– user2669043
Dec 4 '13 at 3:21