SQL Assistance

Question

SQL Assistance

jamesstirling01

Mr or Mrs. 500

Points: 596
More actions
March 29, 2018 at 6:11 am

#344522

Hi,
I have imported our customer & job database from Access into SQLv17.
In my job info table i have 2 coloums in my job info table that has a date reported and a time reported and example it looks like this:
Date Reported   | Time Reported
05/12/2014           |    1899-12-30 10:27:59.000
05/12/2014           |    1899-12-30 10:14:46.000
There are over 29602 rows to the job info table relating to work done on the support desk.
My question is i know the data is old and out of date as i have taken this from a old historical valid backup rather than getting data from the main DB that is used daily and then plan to re-import the most update data once i have developed the new support page in PHP.
I would like to change the Time Reported coloum to keep the correct time and remove the 1899-12-30 as this is not required.
Once i have this i can look at getting querys to pull back number of open tickets and tickets logged today but i beleive tickets today will be hard due to the age of the data.
Is this possible and does anyone have information or suggestions going forward.
I am new to SQL i have installed and adminstrated the software from a IT support role but never worked on a DB as DB administrator and make sure all is correct.
Any question please ask.
Thanks in-advance
James

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

jamesstirling01 - Thursday, March 29, 2018 6:11 AM
Hi,
I have imported our customer & job database from Access into SQLv17.
In my job info table i have 2 coloums in my job info table that has a date reported and a time reported and example it looks like this:
Date Reported   | Time Reported
05/12/2014           |    1899-12-30 10:27:59.000
05/12/2014           |    1899-12-30 10:14:46.000
There are over 29602 rows to the job info table relating to work done on the support desk.
My question is i know the data is old and out of date as i have taken this from a old historical valid backup rather than getting data from the main DB that is used daily and then plan to re-import the most update data once i have developed the new support page in PHP.
I would like to change the Time Reported coloum to keep the correct time and remove the 1899-12-30 as this is not required.
Once i have this i can look at getting querys to pull back number of open tickets and tickets logged today but i beleive tickets today will be hard due to the age of the data.
Is this possible and does anyone have information or suggestions going forward.
I am new to SQL i have installed and adminstrated the software from a IT support role but never worked on a DB as DB administrator and make sure all is correct.
Any question please ask.
Thanks in-advance
James

Can you share the data types for Date Reported and Time Reported? Would it help if they were on a single column or do you need them on separate columns? To answer that, you need to find out how often will the time be the main factor and not the date.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

SELECT *, Combined = DATEADD(DAY, DATEDIFF(DAY, -2, TheDate), TheTime) FROM ( VALUES ('05/12/2014', '1899-12-30 10:27:59.000'), ('05/12/2014', '1899-12-30 10:14:46.000') ) History(TheDate, TheTime)
TheDate TheTime Combined
05/12/2014 1899-12-30 10:27:59.000 2014-05-12 10:27:59.000
05/12/2014 1899-12-30 10:14:46.000 2014-05-12 10:14:46.000