SQL Server 2005 datetime value as 0

I tried to assign value 0 to the datetime field in SQL Server 2005. I 
noticed the value in this table field was stored as
1900-01-01 00:00:00:000

However, when I retrieve this value and do a comparison:
if FieldByName('TimeField').AsDateTime = 0 then
  Showmessage('0')
else
  Showmessage('not 0');

The result always shows it is 'not 0'.

Any idea?
0
Pak
10/23/2008 5:33:46 AM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

27 Replies
747 Views

Similar Articles

[PageSpeed] 39

Hi Pak,

> I tried to assign value 0 to the datetime field in SQL Server 2005. 

Why not NULL instead of 0, if all you want to do is indicate an invalid 
(or unassigned) date?

> However, when I retrieve this value and do a comparison:
> if FieldByName('TimeField').AsDateTime = 0 then
>   Showmessage('0')
> else
>   Showmessage('not 0');
> 
> The result always shows it is 'not 0'.

What is the value of FieldByName('TimeField').AsDateTime in that case?

Groetjes,
           Bob Swart

-- 
Bob Swart Training & Consultancy (eBob42.com) Forever Loyal to Delphi
CodeGear Technology Partner -- CodeGear RAD Studio Reseller (BeNeLux)
Delphi Win32 & .NET books on Lulu.com: http://stores.lulu.com/drbob42
Personal courseware + e-mail support http://www.ebob42.com/courseware
Blog: http://www.drbob42.com/blog - RSS: http://eBob42.com/weblog.xml
0
Bob
10/23/2008 6:25:19 AM
Delphi's TdateTime type evelauates 0 to 
12/30/1899
So when SQL server Date of is translated to TDateTime it should be a 2



> {quote:title=Pak Tse wrote:}{quote}
> I tried to assign value 0 to the datetime field in SQL Server 2005. I 
> noticed the value in this table field was stored as
> 1900-01-01 00:00:00:000
> 
> However, when I retrieve this value and do a comparison:
> if FieldByName('TimeField').AsDateTime = 0 then
>   Showmessage('0')
> else
>   Showmessage('not 0');
> 
> The result always shows it is 'not 0'.
> 
> Any idea?
0
Brian
10/23/2008 11:21:14 PM
>> I tried to assign value 0 to the datetime field in SQL Server 2005.
>
> Why not NULL instead of 0, if all you want to do is indicate an invalid
> (or unassigned) date?
>

If I assign NULL to the parameter, Delphi raised exception because that 
parameter has no value assigned.
0
Pak
10/24/2008 6:11:05 AM
Pak Tse wrote:

> If I assign NULL to the parameter, Delphi raised
> exception because that parameter has no value assigned.

Use ParamByName('AParam').Clear;

-- 
Bill Todd (TeamB)
0
Bill
10/24/2008 2:42:19 PM
Bill Todd wrote:
> Use ParamByName('AParam').Clear;

I don't think there is a clear.  IIRC, there is a special variant
constant you assign to set it to null.  can't remember what it is off
the top of my head.  Maybe null in the variants unit.


-- 
Iman
0
Iman
10/24/2008 8:05:59 PM
Iman Crawford wrote:

> I don't think there is a clear.  IIRC, there is a special
> variant constant you assign to set it to null.  can't
> remember what it is off the top of my head.  Maybe null
> in the variants unit.

You are right. ADO is different. The constant may be
varNull. I cannot remember for sure either.

-- 
Bill Todd (TeamB)
0
Bill
10/24/2008 9:08:31 PM
Try

....ParamByName('AParam').Valeu := Variants.Null;

-- 
Bill Todd (TeamB)
0
Bill
10/24/2008 9:43:57 PM
> Try
>
> ...ParamByName('AParam').Valeu := Variants.Null;

I found
...ParamByName('AParam').Valeu := varNull;

But the value still stored as
1900-01-02 00:00:00.000

I just wonder it is pretty common to NOT assign value to a datetime field.
How you guys deal with that when you insert a record into SQL Server table 
datatime field when there is no value to be assigned?
0
Pak
10/26/2008 11:07:37 PM
>> Delphi's TdateTime type evelauates 0 to
>> 12/30/1899
>> So when SQL server Date of is translated to TDateTime it should be a 2
>>
>
> So any idea why SQL Server did not save as
> 1899-12-30
> when I used
> ParamByName('TimeField').Value = 0;
> But it saved as
> 1900-01-02 00:00:00.000
> ?

Hi,
I just wonder it should be 3 when translates to TDateTime?
0
Pak
10/26/2008 11:35:12 PM
Try

....ParamByName('AParam').Valeu := Unassigned;

and see if that works.

-- 
Bill Todd (TeamB)
0
Bill
10/27/2008 12:02:41 AM
> Try
>
> ...ParamByName('AParam').Valeu := Unassigned;
>
> and see if that works.

Hi Bill,

No luck.
Runtime error:
"Parameter object is improperly defined. Inconsistent or incomplete 
information was provided."
0
Pak
10/27/2008 3:37:31 AM
I am out of ideas.

-- 
Bill Todd (TeamB)
0
Bill
10/27/2008 12:54:48 PM
Because Delphi is converting the value from TdateTime to the ADO field type for Date
You can look at ADODB.pas and DB.Pas if you want to pursue this any further.


So any idea why SQL Server did not save as
1899-12-30
when I used
ParamByName('TimeField').Value = 0;
But it saved as
1900-01-02 00:00:00.000
?

Edited by: Brian Bushay on Oct 27, 2008 5:17 PM
0
Pak
10/28/2008 12:20:30 AM
Pak Tse skrev:
> I tried to assign value 0 to the datetime field in SQL Server 2005. I 
> noticed the value in this table field was stored as
> 1900-01-01 00:00:00:000
> 
> However, when I retrieve this value and do a comparison:
> if FieldByName('TimeField').AsDateTime = 0 then
>   Showmessage('0')
> else
>   Showmessage('not 0');
> 
> The result always shows it is 'not 0'.
> 
> Any idea?

Hi!

I did a little test. For me it behaves as expected.
I get 1900-01-01 00:00:00:000 when the parameter data type is ftInteger 
and I get 1899-12-30 00:00:00.000 when the paramter data type is ftDateTime.

regards

/Micke

{code}
procedure TForm1.Button1Click(Sender: TObject);
begin
   ADOCommand1.ParamCheck := False;
   ADOCommand1.CommandText := 'insert into Table1 values (?)';
   ADOCommand1.Parameters.AddParameter;

   ADOCommand1.Parameters[0].DataType := ftInteger;
   ADOCommand1.Parameters[0].Value := 0;
   ADOCommand1.Execute;	// 1900-01-01 00:00:00.000

   ADOCommand1.Parameters[0].DataType := ftDateTime;
   ADOCommand1.Parameters[0].Value := 0;
   ADOCommand1.Execute;	// 1899-12-30 00:00:00.000

   ADOCommand1.Parameters[0].Value := Null;
   ADOCommand1.Execute;	// NULL
end;
{code}
0
Mikael
10/28/2008 7:05:45 AM
>   ADOCommand1.Parameters[0].DataType := ftDateTime;
>   ADOCommand1.Parameters[0].Value := 0;
>   ADOCommand1.Execute; // 1899-12-30 00:00:00.000
>
>   ADOCommand1.Parameters[0].Value := Null;
>   ADOCommand1.Execute; // NULL

I use TADOQuery descendent, that is inhouse components.

if I use
Query1.Parameters[0].Value := Null;
Query1.Execute;
The datetime field value is 1900-01-01 00:00:00.000
0
Pak
10/29/2008 2:07:12 AM
> {quote:title=Pak Tse wrote:}{quote}
> I use TADOQuery descendent, that is inhouse components.
> 
> if I use
> Query1.Parameters[0].Value := Null;
> Query1.Execute;
> The datetime field value is 1900-01-01 00:00:00.000

Do you have 0 as default value defined for this field in the database?
0
Mikael
10/29/2008 6:17:58 AM
Pak Tse wrote:

> I use TADOQuery descendent, that is inhouse components.

Query1.Parameters[0].Clear should work.

You should really use TAdoDataset instead of TAdoQuery unless you are
porting a BDE app to use ADO components.

-- 
Iman
0
Iman
10/29/2008 3:56:20 PM
>> I use TADOQuery descendent, that is inhouse components.
>
> Query1.Parameters[0].Clear should work.
>
> You should really use TAdoDataset instead of TAdoQuery unless you are
> porting a BDE app to use ADO components.

We had created in house VCL descendent from TADOQuery and have to use it 
anyway.
Query1.Parameters[0].Clear
did not work because no such method for TADOQuery.

I need to use this TADOQuery descendent to insert record to make our 
application consistency. If I don't use this TADOQuery for insertion, then 
it seems TADOQuery is mainly useful for SELECT.....?
0
Pak
10/29/2008 10:14:01 PM
> Do you have 0 as default value defined for this field in the database?
I have not set any default in this datetime field.

In my insert query there is a parameter to insert this datetime field. I 
need to pass/supply value for this parameter.
0
Pak
10/29/2008 10:15:46 PM
> {quote:title=Pak Tse wrote:}{quote}
> >> I use TADOQuery descendent, that is inhouse components.
> >
> > Query1.Parameters[0].Clear should work.
> >
> > You should really use TAdoDataset instead of TAdoQuery unless you are
> > porting a BDE app to use ADO components.
> 
> We had created in house VCL descendent from TADOQuery and have to use it 
> anyway.
> Query1.Parameters[0].Clear
> did not work because no such method for TADOQuery.
> 
> I need to use this TADOQuery descendent to insert record to make our 
> application consistency. If I don't use this TADOQuery for insertion, then 
> it seems TADOQuery is mainly useful for SELECT.....?

Use two TadoQuery components.  One with a Date parameter when you have one and another wtih out the date parameter when you have a null date.
0
Brian
10/30/2008 1:18:42 AM
Pak Tse wrote:

> I need to use this TADOQuery descendent to insert record to make our
> application consistency. If I don't use this TADOQuery for insertion,
> then it seems TADOQuery is mainly useful for SELECT.....?

TAdoQuery more for BDE compatiblity.

TAdoDataset mainly for selects
TAdoCommand for anything you want. 

We tested TAdoDataset.Parameters[0].Value := Variants.Null and it
worked as expected.  Informix database.  Maybe you have a driver issue
(I've run into that quite often with OleDb).

-- 
Iman
0
Iman
10/30/2008 3:42:25 PM
There's somthing called EmptyParam - does this do the trick?

-- 
Regards,

Ronald Hoek

Applicationdevelopper
ComponentAgro B.V.
0
Ronald
12/2/2008 3:02:17 PM
What are you exactly doing?

Are us using a parameterized query or are you using a SQL command or Stored 
procedure...

It would be nive to have some extra info on what you're trying to 
accomplish.

-- 
Regards,

Ronald Hoek

Applicationdevelopper
ComponentAgro B.V.
0
Ronald
12/2/2008 3:05:05 PM
Bob, Bill, Iman, Pak Tse,

What I don't like about the way modern programming is 
heading is that we are seeing more of the "try this" type 
of advice which often or usually does not work.  

Is there any hope for getting back to the basic "this *will* 
work" or "do this to fix your problem"?

Regards, JohnH
0
John
12/2/2008 3:48:58 PM
John Herbster wrote:

> Is there any hope for getting back to the basic "this
> will work" or "do this to fix your problem"?

Absolutely no hope at all.<g> 

-- 
Bill Todd (TeamB)
0
Bill
12/2/2008 4:08:21 PM
John Herbster wrote:

> Is there any hope for getting back to the basic "this will 
> work" or "do this to fix your problem"?

Not when you're relying on a black box library.

-- 
Iman
0
Iman
12/5/2008 5:55:14 PM
if FieldByName('TimeField').AsFloat = 0 then
  Showmessage('0')
 else
  Showmessage('not 0');


Pak Tse wrote:

> I tried to assign value 0 to the datetime field in SQL Server 2005. I
> noticed the value in this table field was stored as 1900-01-01
> 00:00:00:000
> 
> However, when I retrieve this value and do a comparison:
> if FieldByName('TimeField').AsDateTime = 0 then
>  Showmessage('0')
> else
>  Showmessage('not 0');
> 
> The result always shows it is 'not 0'.
> 
> Any idea?
0
Joachim
12/5/2008 7:17:43 PM
Reply:

Similar Artilces:

SQL server 2005 and SQL server 2005 lite
Hi, I have a web site hosted at a hosting company.. I'm in the process of designing a web site which has some forms that are going to hit a database, etc.. for a small company.. This small company has it's own Web server, and when I'm finished I'll copy the finsihed product to their IIS web server, etc.. Well, I have SQL server 2005 full edition as part of my web hosting, but I'm going to have to use SQL server Lite for the end user / client since they don't have SQL server. So, I'm wondering if I can do my work on MY remote host using the full edition of SQL server 2005, and then copy to th...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf file...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi,Basically i built a webpage with Login and it's working well from my local machine.Right now, i'm using Database Publishing Wizard. the problem is how do i convert.MDF files to .SQL 2005 to get my database publish in the internet? thanks  Place your .MDF file in the App_Data directory, change the connection string accordingly and then publish your web site. ----------------------------------------------------------Please click Mark As Answer if this helped in solving your problem.  Please click "Mark As Answer" if this hepled in solving your problem. ...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf files ...

How to replace DateTime field with null value in SQL 2005 server
How to replace DateTime field with null value in SQL 2005 server I create a stored procedure aa, It works well, but sometimes I hope to replace CreateDate field with null value,I don't know how to doIt seems that datetime type is not null value create aa  @CreateDate DatetimeAsUpdate cw set CreateDate=@CreateDate    SuperCool Multiple ZIP - A utility to unzip multiple files and work with multiple zip filesSuperCool Random Number Generator mycwcgr:It seems that datetime type is not null value Can you clarify this statement a bit more please? Do you mean that the colum...

SQL Server 2000 and SQL Server 2005 in the same server
Can enyone please send me a link to a white paper explaining how to run both SQL Server 2000 and 2005 in the same machine?Thanks. I don't know about the white paper but I had both running on my machine for a while by making the SQL 2000 a named instance. So for SQL 2005 connection strings my server name was (local) while for SQL 200 using the named instance it was (local)\joesql where joesql was the instance nameHope it helps,Joe http://www.joeaudette.comhttp://www.mojoportal.com...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above.
Can anybody point me in the direction of how to resolve this error, Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above. I am running Visusl Studio 2005, SQL Server 2005 Standard, running on WIndows XP Pro. I am trying to use SQL for session state management. I originally ran the following code from the command line prompt, aspnet_regsql.exe -E -ssadd -sstype p This created an ASPState database. I then manually added my default ASP.NET login as a user. In my web.confi...

Move from SQL Server 2000 to SQL Server 2005 with VS 2005
 We have a new database server for SQL Server 2005.  To use our old VS 2005 websites, which were connected toanother database server with SQL Server 2000, what should I do?  Just change the server name of the connection stringin the Source code and/or Web.config?  My preliminary testing seemed to be not effective.TIA,Jeffrey wonjartran:  We have a new database server for SQL Server 2005.  To use our old VS 2005 websites, which were connected toanother database server with SQL Server 2000, what should I do?  Just change the server name of the connectio...

Sql Server 2005 Express or Sql Server 2005 Developer Edition
Hi all, I just installed my VS 2005 Pro (full installation) and it installed Sql Server Express 2005.  I also have a CD for Sql Server 2005 Developer Edition which I have not installed yet.  My need for Sql Server on my development machine is for building and testing apps - just myself - no users will be hitting my sql server on my PC.  Most of the time I hit my web server's sql server but also want the engine on my PC for when I'm offline. Question: Should I install the developer edition and remove sql server express?  Doesn't the developer edition more close...

Moving SQL Server 2005 Database to SQL Server 2005 Express
Hi, I have SQL Server 2005 in my Development machine but the hosting server has only SQL server 2005 Express. I was wondering if I develop the DB in SQL server 2005 would be able to move/deploy it to SQL Server Express in the production server? If so how? Can I just generate the script and then run it on the productions server? Or there is a better/easier way? Thanks in advanceShuaib----------------------- I don't anticipate any problems creating database in SQL Server 2005 and moving it to Express. If you need to generate the script to create an empty shell (no d...

Confliction between SQL Server 2005 and SQL Server 2005 Express Edition
Hi,   My objective is using a single DB file (mdf file)  + SQL Server Express 2005 to run my program   I got two softwares here, Visual Studio 2005 and SQL Server 2005. Firstly, I installed Visual Studio 2005, normally the SQL Server 2005 Express Edition will be installed with this software. After finishing the installation. I opened a new project and try to open a DB file (.mdf) from Visual Studio 2005, it works. The problem is that I can't restore the mdf file from an old database file since it lose a lot of functions that appear in SQL Server 2000 / SQL Server 2005. ...

This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers
When I connect SQL Server 2005 to a remote database i get the error: "This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers". Can I confirm it is because: the remote db is of SQL Server 2008? There is a soln mentioned in http://forums.asp.net/t/1324047.aspx Did anybody try this (installing 2008 express: http://www.microsoft.com/express/sql/download/)? I am just checking again, whether it will disturb my existing SQL Server installation, in any means...? Though it is mentioned that it will g...

SQL Server 2005 Installation problems with SQL Server Express & SQL Server 2000
Hi Guys, I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well.  (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!) Everything went fine except that nothing seemed to be installed.  I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing. A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005. So after a number o...

Web resources about - SQL Server 2005 datetime value as 0 - embarcadero.delphi.ado

Is there a JQuery plugin to convert UTC datetimes to local user timezone?
If I have a tag: &lt;span class="utctime"&gt;2010-01-01 11:30 PM&lt;/span&gt; I would like a jquery script or plug in to convert every utctime ...


Targetprocess 3.7.3: Epic unit for Bug/Story cards, new DateTime functions in Webhook plugin
Webhook plugin supports new DateTime functions AddDays(DateTime date, int days) – Returns a new date with the number of days added. The number ...

Common Pitfalls when working with DateTime’s
In .NET, the DateTime structure provides us wonderful functionality, but this seemingly simple structure can cause a lot of headaches if you ...

Parsing Twitter Date format to .Net DateTime
Recently when I needed to parse the twitter datetime format into a .Net DateTime I found this post on the web showing how to do it. However, ...

C Sharp (programming language) - Wikipedia, the free encyclopedia
(pronounced see sharp ) is a multi-paradigm programming language encompassing strong typing , imperative , declarative , functional , generic ...

1753 - Wikipedia, the free encyclopedia
Language: English Afrikaans አማርኛ العربية Aragonés Asturianu Aymar aru Azərbaycanca বাংলা Bân-lâm-gú Basa Banyumasan Беларуская ‪Беларуская (тарашкевіца)‬ ...

Coded Smorgasbord: Schizophrenic Haiku Comments and More
... writes Malcom StandardResponse UnSubscribeNewsletterUserAccount( string opTinGUID,string email, string sellingRegion, string source, DateTime ...

Limburg.be - Welkom op de site van de provincie Limburg - Welkom_op_de_site_van_de_provincie_Limburg
visit wonen werken leven ontspannen infopunt home op trefwoord contactpersonen wegwijs sitemap print deze pagina Je bent hier: limburg.be Contact ...

Simples Ideias. Por Nando Vieira.
Simples Ideias Siga-me no Twitter Junho/2012 Usando o Vagrant como ambiente de desenvolvimento no Windows http://simplesideias.com.br/usando ...

Resources last updated: 12/20/2015 10:06:19 AM