Cannot insert now() into a table

[Guy Cohen]

Hi all
as part of Insert I use Now()
The type in the database is shortdatetime.
What is the correct format to insert now()?
Guy

 

[Mark Rae]

The type in the database is shortdatetime.

What RDBMS you're using...? You mention shortdatetime, so I'm assuming it's
not SQL Server...

What is the correct format to insert now()?

What database connectivity you're using...?

What ADO.NET object(s) are you using...?

as part of Insert I use Now()

What exactly is 'Insert' in this context...?

 

[Guy Cohen]

Hi Mark
Thanks for trying.
I use MS SQL Server Express 2005 and ASP.NET 2

The query is:
INSERT INTO mytable (X,Y,Z) VALUES(25,'09/10/2006
15:46:01','11111111','LALALA')

Y is defined as shortdatetime

"The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.

The statement has been terminated."




Guy

 

[Mark Rae]

Thanks for trying.
I use MS SQL Server Express 2005 and ASP.NET 2

The query is:
INSERT INTO mytable (X,Y,Z) VALUES(25,'09/10/2006
15:46:01','11111111','LALALA')

Y is defined as shortdatetime

That's why I wondered what RDBMS you're using! Is there actually a
shortdatetime date type in SQL Server Express 2005...? There certainly isn't
in the retail version...

"The conversion of char data type to smalldatetime data type resulted in
an out-of-range smalldatetime value.

The statement has been terminated."

Firstly, are you absolutely sure you have the fields in the right order...

Assuming you have, do either of the following SQL statements work...?

INSERT INTO mytable (X,Y,Z) VALUES(25,'09 Oct 2006
15:46:01','11111111','LALALA')

INSERT INTO mytable (X,Y,Z) VALUES(25,'getdate()','11111111','LALALA')

 

[Mark Rae]

INSERT INTO mytable (X,Y,Z) VALUES(25,'getdate()','11111111','LALALA')

Sorry, that should be:

INSERT INTO mytable (X,Y,Z) VALUES(25,getdate(),'11111111','LALALA')

 

[Griff]

The query is:
INSERT INTO mytable (X,Y,Z) VALUES(25,'09/10/2006
15:46:01','11111111','LALALA')

Y is defined as shortdatetime

Shortdatetime or smalldatetime?

In the past (with classic ADO & SQL Server) I've found that there can be
some confusion between the date formats and therefore always passed the date
in the format: yyyy-MMM-dd hh:mm:ss, e.g. '2006-Oct-09 15:46:01'.

One thing that I didn't quite understand....you're populating X, Y & Z with
4 values...."24", "Date", "1111111" and "lalala".

Griff

 

[Guy Cohen]

Hi again Mark

The correct solution is to format the time part of now to HH:mm
If you look in my original query I included the seconds which is not
supported by shortdatetime.
I thought it will auto-cast it....
NOT

Thanks again sire!!!!
Guy

 

[Mark Rae]

The correct solution is to format the time part of now to HH:mm
If you look in my original query I included the seconds which is not
supported by shortdatetime.

Does SQL Server 2005 Express *really* refer to the smalldatetime datatype as
shortdatetime...?

 

[Guy Cohen]

my mistake:
its smalldatetime
Guy

Does SQL Server 2005 Express *really* refer to the smalldatetime datatype
as shortdatetime...?

 

[Mark Rae]

Shortdatetime or smalldatetime?

According to the OP, he meant smalldatetime...

In the past (with classic ADO & SQL Server) I've found that there can be
some confusion between the date formats and therefore always passed the
date in the format: yyyy-MMM-dd hh:mm:ss, e.g. '2006-Oct-09 15:46:01'.

Absolutely! Unambiguous date formats - you can't beat 'em

One thing that I didn't quite understand....you're populating X, Y & Z
with 4 values...."24", "Date", "1111111" and "lalala".

Yes - I wondered about that too, but took it to be another typo...