Click here to Skip to main content
14,867,831 members
Home / Discussions / Database
   

Database

 
QuestionConvert date, did I go too far Pin
jkirkerx8-Apr-21 12:50
professionaljkirkerx8-Apr-21 12:50 
AnswerRe: Convert date, did I go too far Pin
Victor Nijegorodov8-Apr-21 20:22
MemberVictor Nijegorodov8-Apr-21 20:22 
AnswerRe: Convert date, did I go too far Pin
Richard Deeming8-Apr-21 22:03
mveRichard Deeming8-Apr-21 22:03 
GeneralRe: Convert date, did I go too far Pin
jkirkerx9-Apr-21 6:14
professionaljkirkerx9-Apr-21 6:14 
GeneralRe: Convert date, did I go too far Pin
Mycroft Holmes9-Apr-21 12:21
professionalMycroft Holmes9-Apr-21 12:21 
GeneralRe: Convert date, did I go too far Pin
jkirkerx9-Apr-21 14:20
professionaljkirkerx9-Apr-21 14:20 
GeneralRe: Convert date, did I go too far Pin
Richard Deeming11-Apr-21 21:10
mveRichard Deeming11-Apr-21 21:10 
AnswerRe: Convert date, did I go too far, Sort of solved Pin
jkirkerx10-Apr-21 14:06
professionaljkirkerx10-Apr-21 14:06 
I ended up with this. I didn't want to do too much change at one time, and focused on preserving the date formats that feed into the SQL statements via PHP. eg. '2021-04-10'. I made sure that the month and day were all like 04 instead of 4.

And my head scratchier was a pretty stupid mistake; I translated the code late in the month, so the day was 21. When the month rolled over the day 08 was 8. I went back through my PHPV7.4 and fixed how I crafted the dates.

This was just one of probably 30 SQL statements I fixed. But now my results are matching the original code written in 2003.
SELECT 
     a.project_no, 
     a.status, 
     a.Sales_no, 
     a.swan_job, 
     b.tc, 
     b.EC, 
     convert(char(10), b.startup_check_date, 120), 
     convert(char(10), b.finished_check_date, 120), 
     e.Employee_ID, 
     e.fname, 
     e.lname, 
     c.customer_no, 
     c.lname, 
     c.fname 
 FROM project AS a, commission_summary AS b, employee AS e, customer AS c 
 WHERE a.project_no = b.project_no AND a.customer_no = c.customer_no 
 AND a.sales_no = e.Employee_ID 
 AND (a.status ='construction' 
 OR a.status ='finished') 
 AND ((CONVERT(char(10), b.startup_check_date, 120) >= '$firstDate' AND CONVERT(char(10), b.startup_check_date, 120) <= '$lastDate') 
 OR (CONVERT(char(10), b.finished_check_date, 120)  >= '$firstDate' AND CONVERT(char(10), b.finished_check_date, 120) <= '$lastDate') 
 OR (b.startup_check_date IS NULL OR CONVERT(char(10), b.startup_check_date, 120) = '1900-01-01') 
 OR ((CONVERT(char(10), b.startup_check_date, 120) <= '$firstDate') 
 AND (b.finished_check_date IS NULL OR CONVERT(char(10), b.finished_check_date, 120) = '1900-01-01')))

If it ain't broke don't fix it
Discover my world at jkirkerx.com

QuestionExcel Spreadsheet To Database Migration Assistance Pin
Glen McHale6-Apr-21 23:55
MemberGlen McHale6-Apr-21 23:55 
SuggestionRe: Excel Spreadsheet To Database Migration Assistance Pin
CHill607-Apr-21 0:57
mveCHill607-Apr-21 0:57 
AnswerRe: Excel Spreadsheet To Database Migration Assistance Pin
Mycroft Holmes7-Apr-21 12:54
professionalMycroft Holmes7-Apr-21 12:54 
QuestionSQLite query Pin
David Crow23-Mar-21 10:02
MemberDavid Crow23-Mar-21 10:02 
AnswerRe: SQLite query Pin
k505423-Mar-21 14:42
mvek505423-Mar-21 14:42 
GeneralRe: SQLite query Pin
David Crow23-Mar-21 16:10
MemberDavid Crow23-Mar-21 16:10 
AnswerRe: SQLite query Pin
Richard Deeming23-Mar-21 23:31
mveRichard Deeming23-Mar-21 23:31 
GeneralRe: SQLite query Pin
David Crow24-Mar-21 3:16
MemberDavid Crow24-Mar-21 3:16 
QuestionDisallowed implicit conversion from data type smalldatetime to data type float Pin
jkirkerx18-Mar-21 11:56
professionaljkirkerx18-Mar-21 11:56 
AnswerRe: Disallowed implicit conversion from data type smalldatetime to data type float Pin
Victor Nijegorodov18-Mar-21 21:15
MemberVictor Nijegorodov18-Mar-21 21:15 
GeneralRe: Disallowed implicit conversion from data type smalldatetime to data type float Pin
jkirkerx19-Mar-21 6:58
professionaljkirkerx19-Mar-21 6:58 
GeneralRe: Disallowed implicit conversion from data type smalldatetime to data type float Pin
jsc4219-Mar-21 7:45
professionaljsc4219-Mar-21 7:45 
GeneralRe: Disallowed implicit conversion from data type smalldatetime to data type float Pin
jkirkerx19-Mar-21 8:54
professionaljkirkerx19-Mar-21 8:54 
GeneralRe: Disallowed implicit conversion from data type smalldatetime to data type float Pin
jsc4219-Mar-21 11:17
professionaljsc4219-Mar-21 11:17 
GeneralKearning Python Pin
Lumbeta Aquirok17-Mar-21 23:50
MemberLumbeta Aquirok17-Mar-21 23:50 
GeneralRe: Kearning Python Pin
Richard MacCutchan18-Mar-21 0:01
mveRichard MacCutchan18-Mar-21 0:01 
QuestionSystem.NullReferenceException: Pin
H. Martins23-Feb-21 14:28
MemberH. Martins23-Feb-21 14:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.