?
Solved

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

Posted on 2003-03-23
3
Medium Priority
?
488 Views
Last Modified: 2008-02-07
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
Comment
Question by:jvescio
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8191038
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
 
LVL 13

Accepted Solution

by:
ispaleny earned 2000 total points
ID: 8191361
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
 

Author Comment

by:jvescio
ID: 8194440
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question