Insert TIME only in DateTime field

I am doing a temporary retro-upgrade right now. So, I know this isn't exactly in the scope of ASP.Net. Ordinally my posts are. However, I need a VBScript example of how to insert the Date only into the DateTime field of an SQL 2000 Server. By default, if you try to, the server automatically adds the date "1/1/1900". Can anyone help me please?That is how datetime fields work. You need to address this using formatting on the client side. DateTime has a number of useful ToString() overloads.|||Thanks for your help. It is too bad Access doesn't function in datatypes more similarly to SQL, especially since they are products of the same company. This migration would go alot easier. I am migrating some VBScript applications from Access to SQL, then from VBScript to VB.Net. It has been quite fun so far. Hehehe...

Insert statement for datetime column fails

I ve a simple table with a column of type datetime. I ve successfully inserted the following values in it,

2006-09-13 18:00:10
2006-09-14 18:00:10
2006-09-15 18:00:10

however, it fails when i try to insert the value 0000-00-00 00:00:00. ie., the following insert statement fails

INSERT INTO TEST VALUES('0000-00-00 00:00:00')

The error thrown is,

Server Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated.Obviously SQL Server thinks that '0000-00-00 00:00:00' is not a valid date and I couldn't agree more.
You should use NULL to "mark" a column's value as absent, not some strange (invalid) value|||hi shammat,

thanks for the reply.. whats the least possible valid day i could enter for a datetime column..|||What's wrong with NULL?

Edit:

The lowest value is documented in the manual:
http://msdn2.microsoft.com/en-us/library/ms187819.aspx|||Hi shammat,

I ve migrated an existing table structure and its data from MySQL to SQLServer, in MySQL the column is Not NULL and one of the row has the value 0000-00-00 00:00:00. When i tried to create the same in SQLServer i faced such errors..|||This is something I have seen a lot recently.
Why would anybody declare a column as NOT NULL and then put a totally meaningless value in there, just to comply with the NOT NULL constraint.

That sure does not make any sense to me

I do understand that this was not your decision, I'm just wondering why people do such stupid things|||shammat, very nicely stated, i totally agree

the fact that mysql allows a "zero date" sure detracts from its reputation

the fact that mysql programmers would actually utilize it detracts from them even more|||To be fair against the MySQL users:
I have seen this in an Oracle environment as well, they simply used 1970-01-01 instead...

But then - I have seen it only once with Oracle, whereas I tend to see it more often in the MySQL area|||I've always been in favor of using a NULL when possible to mark data with an unknown or an unknowable value, but many systems can't cope with that due to the programming language being used... Many COBOL variants just don't cope with NULL very gracefully, and a number of "4GL" wannabes have the same problems, although they are dressed up in newer clothes.

In defense of the SQL Server choice for minimum date, that wasn't truly their choice... They had to deal with calendar reformations, and simply picked the earliest date that wasn't likely to have problems for most users. The Julian to Gregorian conversion was messy, it wasn't implemented the same way in many places, and wasn't implemented at the same time everywhere... We take it for granted that only timezones need to be considered to determine when 2006-11-01 will occur because the world has only had a couple of calendars since 1800, and all of those calendars conveniently convert to the Gregorian. This has not been the case throughout history.

-PatP|||And a number of "4GL" wannabes have the same problems, although they are dressed up in newer clothes.Understable, but still not nice :)

In defense of the SQL Server choice for minimum date, that wasn't truly their choice...I find the minimum date to be perfectly fine, as a matter of fact a lot better than allowing 0000-00-00.|||the minimum date is not "perfectly fine"

it may be practicable, but it does introduce another form of "three-valued logic"

for instance, if you assign the minimum date to a date_of_birth column in those instances when you do not know the person's date_of_birth, you cannot simply go blithely ahead and calculate the person's current age

well, technically speaking, you could, but it would be wrong

so using the minimum date is far from "perfect"|||the minimum date is not "perfectly fine"
I didn't mean that the usage of it was fine. I totally agree with you that it is nonsens to use special values for this purpose

I meant the restriction for a minimum date value is acceptable. A valid date as the minimum date is "perfectly fine" compate to 0000-00-00|||I just wonder how much code relies of that date "not being there" as 0000-00-00....|||I just wonder how much code relies of that date "not being there" as 0000-00-00....
exactly

and if you should ever need to move the app to another database platform? code changes!!

nothing more fun than coming in to the office on a sunny saturday afternoon to find all occurrences of 0000-00-00 and replace them with 1970-01-01

whereas if you had used NULL in the first place...

:)

INSERT statement for datetime .

In Oracle , v can use select and insert for a date column as below:

for select :
--------------------
select A,to_char(CDATE,'DD-MM-YY:HH:MI:SS AM') from db.table1;

for insert:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
insert into db.table1
(A,CDATE)
values
( 2, TO_Date('20/01/2005 11:08:33 PM', 'DD/MM/YYYY HH:MI:SS AM'));
--------------------

But How v can use datetime in MSSQL Server ?
What are the corresponding select and insert statement in this ?

Any one have idea in this ?

thankshave you checked the manual? because it's in there

SQL Server recognizes date and time data enclosed in single quotation marks (') in these formats:
Alphabetic date formats (for example, 'April 15, 1998')
Numeric date formats (for example, '4/15/1998', 'April 15, 1998')
Unseparated string formats (for example, '19981207', 'December 12, 1998')|||what about time ? Can I insert a date with a time ?
Please help with an example .|||have you tried anything yet?

do you think this will work --

insert into db.table1 (A,CDATE) values ( 2, '20/01/2005 11:08:33 PM' )

or do you think this will work --

insert into db.table1 (A,CDATE) values ( 2, '2005-01-20 23:08:33' )

what do you think?|||oh yes ... it is working....

insert into db.dbo.table1 (A,CDATE) values ( 2, '20-jan-2005 11:08:33 PM' )

Thanks r937.

Insert statement for a datetime field from vb.net code

Hi All,
I have a datetime column in a table on the SQL database. I need to insert
values into the datetime column from vb.net code. Here is my code:
dim nameval, str, qry as string
nameval = "abc"
str = "2005/03/16 14:20"
qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," & "'"
str & "')"
...
...
ocmd.ExecuteNonQuery()
...
...
The error message that I get is as follows:
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated. .Net
SqlClient Data Provider"
The problem I think is due to passing a string for a datetime field. My
question is, if I convert the string to datetype using CDate(str), then I
would have to again convert the date to string in order to form the insert
statement. So, the ultimate result will be again passing a string for the
datetime field!
I know that this is a simple syntax problem, which I don't seem to get right!
Would anybody be able to give me insert statement for the above?
Thanks.
kd
Hi
"2005/03/16 14:20" is not a valid date time value. Either use "2005-03-16
14:20:00" or "20050316 14:20:00"
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:1FF9F499-8589-4964-9F11-731F22E3D673@.microsoft.com...
> Hi All,
> I have a datetime column in a table on the SQL database. I need to insert
> values into the datetime column from vb.net code. Here is my code:
> dim nameval, str, qry as string
> nameval = "abc"
> str = "2005/03/16 14:20"
> qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," &
"'"
> str & "')"
> ...
> ..
> ocmd.ExecuteNonQuery()
> ...
> ...
> The error message that I get is as follows:
> "The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value. The statement has been terminated. .Net
> SqlClient Data Provider"
> The problem I think is due to passing a string for a datetime field. My
> question is, if I convert the string to datetype using CDate(str), then I
> would have to again convert the date to string in order to form the insert
> statement. So, the ultimate result will be again passing a string for the
> datetime field!
> I know that this is a simple syntax problem, which I don't seem to get
right!
> Would anybody be able to give me insert statement for the above?
> Thanks.
> kd
>

Insert small time into SQL Server 2000 table

Im having a lot of trouble inserting a small time value into a table cell. I gave the cell column the data type 'DateTime', i found i couldnt manually insert a time only value such as '12:30 PM' into a column with 'SmallDateTime'. Something about a "SmallDateTime Overflow Error". However if i enter a similar time value into a table column with the data type 'DateTime' it will happily accept it and leave it as entered.

The real problem seems to be when i try to send a time value to that column with my ASP.NET application. Because it inserts the time value and todays date. So that if i send:

12:30 PM

It will be stored as:

15/11/2003 12:30:00 PM

I only want to store the short time, not the date especially not the date that row was created on because thats useless for the purposes of what my application is trying to achieve and just creates problems down the track when selecting rows.

How can i correct this?Both DateTime and SmallDateTime always have date and time components. Both store the value as a decimal number with the whole number part being the date and the fractional part the time. If you only want the time component set the Date part to 1/1/1900.

When I ran '12:30PM' using datetime and smalldatetime it created the value as '1/1/1900 12:30 PM'. Not sure why you're getting today's date or why it complains entering a smalldatetime with '12:30 PM'

Select Top 1
Cast('12:30 PM' as datetime) as DateTime1230,
Cast('12:30 PM' as smalldatetime) as SmallDateTime1230,
Cast(Cast( '12:30 PM' as datetime) as integer) as IntOfDateTime1230,
Cast(Cast( '12:30 PM' as smalldatetime) as integer) as IntOfSmallDateTime1230,
Cast(Cast( '12:30 PM' as datetime) as float) as FracOfDateTime1230,
Cast(Cast( '12:30 PM' as smalldatetime) as float) as FracOfSmallDateTime1230
From SomeTable

returns

DateTime1230SmallDateTime1230IntOfDateTime1230IntOfSmallDateTime1230FracOfDateTime1230FracOfSmallDateTime1230
1900-01-01 12:30:00.0001900-01-01 12:30:00110.520833333333333370.52083333333333337

|||Ok no worries thats a good explanation. Thanks very much.