473,698 Members | 2,393 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

What values can a datetime data type hold other than date and time??

92 New Member
Hi folks,

I have designed a webpage using asp, i have a text box with a calender in the page from which the user will select a date. since the date field is not mandatory, if this field is left un-entered/blank then the SQL server will automatically inserts the default date "1/1/1900" while insertion to the Database.

I am trying to eliminate/replace the default date getting stored to the database while the date field left blank (Note: Per requirement i cannot make the date field manadatory).

So i am trying to write a condition to check if the date field is empty and if YES then replace some value, before the code inserts the form values to the Database.

if date_field = "" then
date_field = ""
end if

The above code still inserts dafault since the datetime data type itself will not allow blank but if i say date_field = 0 then the system errors out since the data type is datetime. Please help with this issue.

Thanks in advance

Regards,
chandru
Oct 26 '09 #1
3 3100
ck9663
2,878 Recognized Expert Specialist
Options:
1. On your front-end (ASP) check if the object value is empty. If it is, store NULL.
2. On the database, use NULL as default value of the column on your table.

Either case, you need to allow NULL value to be stored in the column.

Happy coding!!!

--- CK
Oct 26 '09 #2
chandhseke
92 New Member
Hi Ck,

Thanks for the reply. I understood your first point.

In your second point i have a slight re-confirmation to make so that we both are on the same page.

In your second point of allowing NULLS, do you mean to change the property of the column from not null to null so that accepts Null values... Please advise.

Also if there is any way? that we can make SQL server not to automatically stores its default values. Thanks in advance.. waiting for your response

regards,
Chandru
Oct 27 '09 #3
ck9663
2,878 Recognized Expert Specialist
@chandhseke
Yes.

@chandhseke
If you specify a default value on the column of a table, it will store that default if you don't specify any value that will be stored to it (ie, not included on the column list on the INSERT INTO clause). If you specify a value, it will store the value you specified, not the default value.

To avoid storing the default, always include it on your INSERT INTO column list and specify a value on the SELECT or VALUES clause.

Happy Coding!!!

--- CK
Oct 27 '09 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

2
6965
by: Tatiana Zadiraka | last post by:
I use DB2 8.1 with FixPack 5. In command line for sql I get all DATE columns only in MM-DD-YYYY format. Of course, DATE('20-12-2003') gives me an error SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007 My db config is: Database territory = RU
51
4526
by: jacob navia | last post by:
I would like to add at the beginning of the C tutorial I am writing a short blurb about what "types" are. I came up with the following text. Please can you comment? Did I miss something? Is there something wrong in there? -------------------------------------------------------------------- Types A type is a definition for a sequence of storage bits. It gives the meaning of the data stored in memory. If we say that the object a is an
1
2995
by: scj | last post by:
Hi all, I need to determine the exact type of DateTime column(*) in an Access 97 database. I'm able to do this with an Access 2 database. With ADO.Net and VB.Net (**), I do something like this : Dim oDT As New DataTable() Dim oCnx As OleDbConnection
3
4234
by: Andrew S. Giles | last post by:
Hello, I am importing a flat text file, and putting it into a datagrid for display on a form. Currently the users have their dates and times seperated. I have two fields, therefore in the datatable feeding the datagrid control. Both are of the DateTime Type. How do I get the time field to display only the Time, and not the date, which is apparently the default.
6
12992
by: Dean Slindee | last post by:
I am looking for the "right" way to handle inserting and presenting null date values. Public Const c_NullDate As Date = #12:00:00 AM# If I set the value of a date variable in an SQL Server insert statement to c_NullDate, the insert statement works. When I re-read the row and display the inserted date value in a text box, the string "1/1/1900" is displayed. If one wanted the textbox to display an empty string, how should either the insert...
1
2878
by: Ugur Ekinci | last post by:
Hi , I have two Sql Server 2000 on seperate machines , First one accepts datetime format like ("dd.MM.yyyy hh:mm:ss") And Second one accepts datetime format like ("MM.dd.yyyy hh:mm:ss") 1- Date formats are different because of SQL Collation? 2- Do regional Settings affect Sql date format? 3- (Important) When inserting a datetime into first server there is no problem (15.12.2000 12:12:12) , but when I insert into second server (if...
5
4917
by: Takeadoe | last post by:
Gang - I'm generating date and time variables from scanned forms. Currently, the date and time values are as follows: 06/26/2006 and 11:30 AM. I've written VBA code to combine them into a single string. The resulting variable - datetime - is exported automatically to Access. When it get's there it looks like "06/26/2006 11:30 AM". Problem is, it is a string. If I set up the database before it is populated with records and set the...
0
4097
by: cyberdawg999 | last post by:
Greetings all in ASP land I have overcome one obstacle that took me 2 weeks to overcome and I did it!!!!! I am so elated!! thank you to all who invested their time and energy towards helping me with my problems. Now for my new little problem,I had a problem posting the values from checkbox fields to a database and thats the obstacle I overcame. Now the second part is my new problem is that I want that the next time that page loads for...
1
8273
by: remya1000 | last post by:
I’m using VB.net 2003 application program. I am trying to do a select statement whereby I'm searching between 2 datetime values that are being stored as datetime. records are stored inside Access. For example, I’m searching between 2 datetime So I need to find all the records in between 10:00 AM and 12:30 AM on 2/23/2009. i tried this code
0
8676
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8608
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9161
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8897
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8867
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7732
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4619
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2332
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2006
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.