I have a table record and have two fields definitions namely:

  • TotalActivity datatype DateTime on SQL query,
  • LastActivity datatype decimal

My problem is how do I calculate these on that record? I have tried to use this data format. How do I use this on my query for that record?

enter image description here

SELECT REPLACE(convert(varchar,getdate(),100),year(getdate()),'at')
SELECT substring(convert(varchar,getdate(),114),0,8)
  • Hi Gcobza, Your question is not clear. What are you expecting as the result ? A date that will be the lastActivity + the totalActivity ? Or a String like "21 min at 2020-09-15" ? Please add details of the expected result in your question – Dominique Boucher Oct 14 at 12:37
  • @Dominique, you see that query below is right i want to use it to my table record. So that it can produce correct result. Right now as you see from the screen shot, the output for both LastActivity and TotalActivity are not correct. Why need help from these as to how to use that query to produce on my table, so that those fields should produce correct output. – Gcobza Oct 14 at 12:39
  • Please consider reading this advice – mustaccio Oct 14 at 12:40
  • @Gcobza : I'm having a hard time matching you query, your screenshot and what you want to achieve... The printscreen seems to show normal data to me... if you are expecting a "Select * from..." to show you the date in a specific format, this is not gonna work. SQL store a DateTime field in its own way and if you want to see it in a different format, then you need to define it in your select statement (using replace and substring as you did, but with LastActivity instead of "getdate()". For your TotalActivity, it's a decimal and noting in your script seems to use it – Dominique Boucher Oct 14 at 12:47
  • @Dominique so if i use select statement together with substring and replace that is allowed in sql? because thats all i need in order to execute the query, can you help me there thats all i need just to write it. – Gcobza Oct 14 at 13:03

You can simply run a query like this:

SELECT REPLACE(convert(varchar,LastActivity,100),year(LastActivity),'at') "MyDate" 
FROM YourTable

Right now, you "DateTime" column seems to only contains date (the time is always 00h00) so the output will be something like "Sept 15 at 00:00"

| improve this answer | |
  • the query is working when executing, the problem now its not updating to the record. Meaning when i ran that query, the data type still shows as screen shot. How can i resolve it not to happen. Record now must be update as per this query so that TotalActivity and LastActivity should give correct format. – Gcobza Oct 14 at 13:20
  • Why do you need to "store" the data in this specific format ? The only way to do it would be to have another column in a varchar data type and store the output as a string. If you do this, it will no longer be a "date" so you won't be able to do stuff like "dateadd()" or "year()", etc... It will also affect the way SQL "seach" for the date so if you do "where date =..." it will not work with the string. Why don't you just change the script that get the data from the DB to format it at the moment where it reads it ? – Dominique Boucher Oct 14 at 13:24
  • Yes i think i should do that, thanks so much so far. – Gcobza Oct 14 at 14:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.