Link to home
Start Free TrialLog in
Avatar of doramail05
doramail05Flag for Malaysia

asked on

Set '-' to Date which is MinValue

if the  sa1.actualtime for lab is NULL and the sa1.actualtime for evaluation has value,
it will not show any records for the row, unless sa1.actualtime for lab has value.

while using NULLIF(sa1.actualtime, '-')
it does not show any records at all,

Is there any like if Date is minvalue then replace '-' character in it.
Avatar of Bhavesh Shah
Bhavesh Shah
Flag of India image

Hi,

you can try with this.

IsNull(sa1.actualtime,'-')


did you tried this?

Or Can you explain more in detail?
@Brichsoft
It won't work without data type conversion:
ISNULL(CONVERT(VARCHAR(10),sa1.actualtime),'-')

===

@doramail05
NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.
http://msdn.microsoft.com/en-us/library/ms177562.aspx

>while using NULLIF(sa1.actualtime, '-')
>it does not show any records at all,
Can you post your query?

>Is there any like if Date is minvalue then replace '-' character in it.
What do you mean by minvalue?
@Brichsoft - if sa1.actualtime is compareable with '-' it's already in character type.
I withdraw what I wrote.
Avatar of doramail05

ASKER

aiks

IsNull(sa1.actualtime,'-')

also didnt show data, :~~

daniel :
minvalue as in 0001-01-01 00:00:000  (but it cannot accept unless 1900-01-01 ..etc)
i remember i posted the sql statement,
SELECT 
 
l.lessonno as lessoncode,
sa.actualtime as actualtime,
sa.attendancestatus as attstatus,
sa.lateduration as lateduration,
(
select ISNULL(sa1.actualtime, '-') from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='LAB'
AND m.modulename = @module
) as lab,
(
select ISNULL(sa1.actualtime, '-') from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='Evaluation'
AND m.modulename = @module
) as evaluation,

Open in new window

>minvalue as in 0001-01-01 00:00:000  (but it cannot accept unless 1900-01-01 ..etc)
To be able to operate on dates starting from 0001-01-01 you need to use datetime2 data type.

Are you sure lab and evaluation queries alone return any values at all (with sa1.actualtime in SELECT list and parameters supplied in WHERE clause)?
nope, thats the reason, would prefer if there's a column validation something like ISNULL,

either one of it could be NULL, but notice that the first one (lab) is null, it will lead to the whole row got no data or did not display. is there an inner join clause
Hi,

if something is null then it wont affect to whole row.
pls can you post your full query as I'm assuming this is half query.

Data wont display only if there are any inner join condition on actualtime ?

- Bhavesh
here they are, pasted the part which will continue the final part of the query
start from union,

Daniel,
tried, but notice that datetime2 could not perform substraction.

could be better off , if there;s a, IFDATEISMINVALUE    then, substitute with '-', which i guess dun have the syntax,

UNION

SELECT 
 
l.lessonno as lessoncode,
sa.actualtime as actualtime,
sa.attendancestatus as attstatus,
sa.lateduration as lateduration,
(
select sa1.actualtime from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='LAB'
AND m.modulename = @module
) as lab,
(
select sa1.actualtime from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='Evaluation'
AND m.modulename = @module
) as evaluation,
(
select sa1.actualtime from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='Final Exam'
AND m.modulename = @module
) as finalexam,
(
select sa1.attendancestatus from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='Quiz'
AND m.modulename = @module
) as attstatusQuiz,
(
select sa1.attendancestatus from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='LAB'
AND m.modulename = @module
) as attstatuslab,
(
select sa1.attendancestatus from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='Evaluation'
AND m.modulename = @module
) as attstatuseval,
(
select sa1.attendancestatus from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='Final Exam'
AND m.modulename = @module
) as attstatusfinalexam
    
FROM traineemodule tm 
JOIN StudentAttendance sa ON tm.said = sa.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='Quiz'
AND m.modulename = @module

Open in new window

Hi,

for date datatype, what you can do is

CASE WHEN sa1.actualtime = '0001-01-01 00:00:000' then '--' ELSE Convert(Varchar(30),actualtime,103) end as actualtime


- Bhavesh
@Brichsoft
00:00:000 is not correct format of time.
CASE WHEN sa1.actualtime = '0001-01-01 00:00:00.000' then '--'
ELSE Convert(Varchar(30),sa1.actualtime,103)
END AS actualtime

If sa1.actualtime would be DATE type, this allows only differing by whole days.

==

Is sa1.actualtime character type?
 
SELECT CASE WHEN sa1.actualtime = '0001-01-01 00:00:00.000' then '--' 
ELSE sa1.actualtime 
END AS actualtime

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of prajapati84
prajapati84
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
actually i performed in gridview's rowdatabound,

but its okie.