• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

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
0
jvescio
Asked:
jvescio
1 Solution
 
nigelrivettCommented:
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
0
 
ispalenyCommented:
You are destroing NULL values into '1900-01-01'

For now, you can patch
 InTUEamHour = '::InTUEamHour::'
by
 InTUEamHour = case when convert(datetime,'::InTUEamHour::')='1900-01-01' then NULL else '::InTUEamHour::' end

Later debug your ASP update code.

Good luck !
0
 
jvescioAuthor Commented:
nigelrivett, ispaleny,

Well done!

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

MUCH appreciation for your time!!

vj

0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now