asked on

UPDATE datetime field with NULL value, NOT 12:00AM

THIS IS A TIME SLIP SCENARIO...using ASP and SQL SERVER 2000 DATA TYPE=datetime LENGTH=8

For an employee time slip system I have the following fields in a table called NONEXMPT:

InMONamHour
InTUEamHour
InWEDamHour
InTHURamHour
InFRIamHour
InSATamHour
InSUNamHour
OutMONamHour
OutTUEamHour
OutTHURamHour
OutFRIamHour
OutSATamHour
OutSUNamHour
InMONpmHour
InTUEpmHour
InWEDpmHour
InTHURpmHour
InFRIpmHour
InSATpmHour
InSUNpmHour
OutMONpmHour
OutTUEpmHour
OutWEDpmHour
OutTHURpmHour
OutFRIpmHour
OutSATpmHour
OutSUNpmHour

I am allowing employees to submit time with an ASP form. If fields are left blank, for days in the week not yet worked, a NULL value is passed. Which is what I want.

Here is the form field I am using:
<select size="1" name="InTUEamHour">
<option selected value=""></option>
</select>

If they return to the form, the next day, no value is displayed for the blank fields, which is correct. Here is the select statement used when they return:

SELECT SUBSTRING(CAST(InMONamHour as varchar),12,17) AS InMONamHour, SUBSTRING(CAST(InTUEamHour as varchar),12,17) AS InTUEamHour, SUBSTRING(CAST(InWEDamHour as varchar),12,17) AS InWEDamHour, SUBSTRING(CAST(InTHURamHour as varchar),12,17) AS InTHURamHour, SUBSTRING(CAST(InFRIamHour as varchar),12,17) AS InFRIamHour, SUBSTRING(CAST(InSATamHour as varchar),12,17) AS InSATamHour, SUBSTRING(CAST(InSUNamHour as varchar),12,17) AS InSUNamHour, SUBSTRING(CAST(OutMONamHour as varchar),12,17) AS OutMONamHour, SUBSTRING(CAST(OutTUEamHour as varchar),12,17) AS OutTUEamHour, SUBSTRING(CAST(OutWEDamHour as varchar),12,17) AS OutWEDamHour, SUBSTRING(CAST(OutTHURamHour as varchar),12,17) AS OutTHURamHour, SUBSTRING(CAST(OutFRIamHour as varchar),12,17) AS OutFRIamHour, SUBSTRING(CAST(OutSATamHour as varchar),12,17) AS OutSATamHour, SUBSTRING(CAST(OutSUNamHour as varchar),12,17) AS OutSUNamHour, SUBSTRING(CAST(InMONpmHour as varchar),12,17) AS InMONpmHour, SUBSTRING(CAST(InTUEpmHour as varchar),12,17) AS InTUEpmHour, SUBSTRING(CAST(InWEDpmHour as varchar),12,17) AS InWEDpmHour, SUBSTRING(CAST(InTHURpmHour as varchar),12,17) AS InTHURpmHour, SUBSTRING(CAST(InFRIpmHour as varchar),12,17) AS InFRIpmHour, SUBSTRING(CAST(InSATpmHour as varchar),12,17) AS InSATpmHour, SUBSTRING(CAST(InSUNpmHour as varchar),12,17) AS InSUNpmHour, SUBSTRING(CAST(OutMONpmHour as varchar),12,17) AS OutMONpmHour, SUBSTRING(CAST(OutTUEpmHour as varchar),12,17) AS OutTUEpmHour, SUBSTRING(CAST(OutWEDpmHour as varchar),12,17) AS OutWEDpmHour, SUBSTRING(CAST(OutTHURpmHour as varchar),12,17) AS OutTHURpmHour, SUBSTRING(CAST(OutFRIpmHour as varchar),12,17) AS OutFRIpmHour, SUBSTRING(CAST(OutSATpmHour as varchar),12,17) AS OutSATpmHour, SUBSTRING(CAST(OutSUNpmHour as varchar),12,17) AS OutSUNpmHour, InMONamType, InMonpmType, OutMonamType, OutMonpmType, InTUEamType, InTUEpmType, OutTUEamType, OutTUEpmType, InWEDamType, InWEDpmType, OutWEDamType, OutWEDpmType, InTHURamType, InTHURpmType, OutTHURamType, OutTHURpmType, InFRIamType, InFRIpmType, OutFRIamType, OutFRIpmType, InSATamType, InSATpmType, OutSATamType, OutSATpmType, InSUNamType, InSUNpmType, OutSUNamType, OutSUNpmType, ID,username,pw,posted,lastname,firstname,manager,location,ppend,weekend, ID FROM nonexempt WHERE username = '::username::' AND pw = '::pw::' AND ppend > GETDATE() AND ppend < (GETDATE()+15)

ALL GOOD SO FAR…

If they update the form, for today’s time, and then submit the form, and then return again tomorrow…12:00AM is displayed in place of all of the, once before, NULL values!! NOT WHAT I WANT TO SEE!!

Here is the form field I am using to display the field value and then UPDATE the value:

<option selected value="<%=FP_FieldHTML(fp_rs,"InTUEamHour")%>"><%=FP_FieldHTML(fp_rs,"InTUEamHour")%>
</option>

Here is my update statement:

UPDATE nonexempt SET InMONamType = '::InMONamType::' , InMONamHour = '::InMONamHour::' , InTUEamType = '::InTUEamType::' , InTUEamHour = '::InTUEamHour::' , InWEDamType = '::InWEDamType::' , InWEDamHour = '::InWEDamHour::' , InTHURamType = '::InTHURamType::' , InTHURamHour = '::InTHURamHour::' , InFRIamType = '::InFRIamType::' , InFRIamHour = '::InFRIamHour::' , InSATamType = '::InSATamType::' , InSATamHour = '::InSATamHour::' , InSUNamType = '::InSUNamType::' , InSUNamHour = '::InSUNamHour::' , OutMONamType = '::OutMONamType::' , OutMONamHour = '::OutMONamHour::' , OutTUEamType = '::OutTUEamType::' , OutTUEamHour = '::OutTUEamHour::' , OutWEDamType = '::OutWEDamType::' , OutWEDamHour = '::OutWEDamHour::' , OutTHURamType = '::OutTHURamType::' , OutTHURamHour = '::OutTHURamHour::' , OutFRIamType = '::OutFRIamType::' , OutFRIamHour = '::OutFRIamHour::' , OutSATamType = '::OutSATamType::' , OutSATamHour = '::OutSATamHour::' , OutSUNamType = '::OutSUNamType::' , OutSUNamHour = '::OutSUNamHour::' , InMONpmType = '::InMONpmType::' , InMONpmHour = '::InMONpmHour::' , InTUEpmType = '::InTUEpmType::' , InTUEpmHour = '::InTUEpmHour::' , InWEDpmType = '::InWEDpmType::' , InWEDpmHour = '::InWEDpmHour::' , InTHURpmType = '::InTHURpmType::' , InTHURpmHour = '::InTHURpmHour::' , InFRIpmType = '::InFRIpmType::' , InFRIpmHour = '::InFRIpmHour::' , InSATpmType = '::InSATpmType::' , InSATpmHour = '::InSATpmHour::' , InSUNpmType = '::InSUNpmType::' , InSUNpmHour = '::InSUNpmHour::' , OutMONpmType = '::OutMONpmType::' , OutMONpmHour = '::OutMONpmHour::' , OutTUEpmType = '::OutTUEpmType::' , OutTUEpmHour = '::OutTUEpmHour::' , OutWEDpmType = '::OutWEDpmType::' , OutWEDpmHour = '::OutWEDpmHour::' , OutTHURpmType = '::OutTHURpmType::' , OutTHURpmHour = '::OutTHURpmHour::' , OutFRIpmType = '::OutFRIpmType::' , OutFRIpmHour = '::OutFRIpmHour::' , OutSATpmType = '::OutSATpmType::' , OutSATpmHour = '::OutSATpmHour::' , OutSUNpmType = '::OutSUNpmType::' , OutSUNpmHour = '::OutSUNpmHour::' , status = '::status::' , Timestamp = '::Timestamp::' WHERE (ID = ::ID::)

What can I do to continue passing NULL values for “blank” fields??

These 12:00AM values are making my form look like cr@p!!

EXPERTS, PLEASE HELP!!

I was previously helped by ScottPletcher

nigelrivett

I would suggest normalising this before going any further.

UserID ,
Type , -- in/out
EventDatetime

or

UserID ,
InDatetime ,
OutDatetime

instead of
SELECT SUBSTRING(CAST(InMONamHour as varchar),12,17) AS InMONamHour

--I would do

SELECT right(convert(varchar(19), InMONamHour, 100), 8) AS InMONamHour
as it doesn't depend on any default settings.

for the nulls I assume you are putting a zero date in the field i.e. midnight 1 jan 1900

SELECT case when InMONamHour = '1 jan 1900' then null else right(convert(varchar(19), InMONamHour, 100), 8) end AS InMONamHour

or if you wanted to return spaces
SELECT case when InMONamHour = '1 jan 1900' then replcate(' ',8) else coalesce(right(convert(varchar(19), InMONamHour, 100), 8), replcate(' ',8)) end AS InMONamHour

ASKER CERTIFIED SOLUTION

ispaleny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jvescio

ASKER

nigelrivett, ispaleny,

Well done!

Both suggestions worked, the patch being the "easiest" is the route I have taken.

MUCH appreciation for your time!!

vj