Click here to Skip to main content
14,971,553 members
Home / Discussions / Database
   

Database

 
AnswerRe: Interval schedule Pin
Richard Deeming27-Apr-21 22:08
mveRichard Deeming27-Apr-21 22:08 
GeneralRe: Interval schedule Pin
Member 1462398929-Apr-21 2:38
MemberMember 1462398929-Apr-21 2:38 
QuestionRetail inventory management DB Design Pin
vineet123456712-Apr-21 23:42
Membervineet123456712-Apr-21 23:42 
AnswerRe: Retail inventory management DB Design Pin
Victor Nijegorodov13-Apr-21 0:54
MemberVictor Nijegorodov13-Apr-21 0:54 
AnswerRe: Retail inventory management DB Design Pin
RedDk13-Apr-21 12:10
MemberRedDk13-Apr-21 12:10 
QuestionRe: Retail inventory management DB Design Pin
Eddy Vluggen13-Apr-21 13:00
professionalEddy Vluggen13-Apr-21 13:00 
AnswerRe: Retail inventory management DB Design Pin
Mycroft Holmes14-Apr-21 13:32
professionalMycroft Holmes14-Apr-21 13:32 
QuestionConvert date, did I go too far Pin
jkirkerx8-Apr-21 12:50
professionaljkirkerx8-Apr-21 12:50 
I'm wondering if I went too far in converting all these dates. While I was testing, I think the database server crashed and the connection broke. I know I asked this before about converting date times, and I thought I fixed this in Feb because it was working fine. But I'm back to it again.
C#
SELECT 
a.project_no, 
a.status, 
a.Sales_no, 
a.swan_job, 
b.tc, 
b.EC, 
convert(VARCHAR(10), b.startup_check_date, 120), 
convert(VARCHAR(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(VARCHAR(10), b.startup_check_date, 120) BETWEEN '2021-4-01' AND '2021-4-31') 
OR (convert(VARCHAR(10), b.finished_check_date, 120) BETWEEN '2021-4-01' AND '2021-4-31') 
OR (convert(VARCHAR(10), b.startup_check_date, 120) IS NULL 
OR convert(VARCHAR(10), b.startup_check_date, 120) = '1900-01-01') 
OR (convert(VARCHAR(10), b.startup_check_date, 120) < '2021-4-01') 
AND (convert(VARCHAR(10), b.finished_check_date, 120) IS NULL OR convert(VARCHAR(10), b.finished_check_date, 120) = '1900-01-01') 
ORDER BY a.status, b.finished_check_date, b.startup_check_date 

Original SQL Statement:
I don't understand the FLOOR statement in the cast. Does that make the date at midnight or 0:01
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 ((CAST(FLOOR(CAST(b.startup_check_date AS FLOAT))AS DATETIME) BETWEEN '$firstDate' AND '$lastDate')
OR (CAST(FLOOR(CAST(b.finished_check_date AS FLOAT))AS DATETIME) BETWEEN '$firstDate' AND '$lastDate')
OR (b.startup_check_date is NULL  or b.startup_check_date = '1900-01-01')
OR ((CAST(FLOOR(CAST(b.startup_check_date AS FLOAT))AS DATETIME) <  '$firstDate' )
AND (b.finished_check_date is NULL or b.finished_check_date = '1900-01-01')))";
If it ain't broke don't fix it
Discover my world at jkirkerx.com

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 
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 

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.