SQL Server - how to insert datetime value in sql

how to insert datetime  value in sql.. i got it out of range datetime varaible error ..how i do it

use getdate() function to insert datetime in sql database

insert into tablename(name,createddate) value('aaaname',getdate())

here createddate should be datetime type

Insert <table_name>(ID,Name,VersionDate,VersionSource)

Select 1,'Shobha',getdate(),'Manual

or

Insert <table_name>(ID,Name,VersionDate,VersionSource)

Select 1,'Shobha','09/23/2008','Manual

A column has a datatype and only one datatype.   So, even though you CAST
only in the WHEN/THEN and not in the ELSE, SQL Server will insist that all
rows are of the same datatype.  Therefore, you will get a datatime
everytime, once through an explicit conversion and once through an implicit
conversion.

-Paresh

Hi,

I'd always recommend sending the date in, in a standard ISO format like: yyyy-mm-dd hh:MM:ss This way you won't get these problems.

Been a while since I've done ASP since I moved over to .NET, so I can't quite remember if there is a function to get it into this format (I can remember FormatDateTime() but don't think this will help) - I think I always used a custom function I created to handle formatting dates into this format (using Year(Now()) + '-' + Month(Now()) + ..... etc)

Also look at one of the following:

1. The query should look like below:

SELECT * FROM mysqlfactiva.dbo.programs.start_date
WHERE isdate(start_date) = 0 OR isdate(end_date) = 0

2. The error is likely due to the time portion of the datetime.  It's expecting the time to be specified as 09:33:16 rather than 09.33.16. 

3. use insert function like below:

   insert into MyDateTest99 select convert (varchar(10),'19780129',120)
   insert into MyDateTest99 select convert (varchar(10),'19910112',120)
   insert into MyDateTest99 select convert (varchar(10),dateadd(dd,2,getdate()),120)

4.This may be caused by date format that differs from the SQL2000 instance to SQL7.0 instance. Try to change the DATEFORMAT option like this (can be set to mdy,ydm,and ymd) and test again:

SET DATEFORMAT mdy
GO

-Paresh

Try the following query

insert into Table(Id, Name, Birthdate) Values(1, "Sanjay", getdate())

getDate will insert the current datetime in your datetime field of the table.

Hope it helps.

Hi,

Sql server Default date format is  yyyy-MM-dd. While insert values from code behind, just convert the date to this format....

like this...

string strCom = "insert into tableName values('" + DateTime.Now.ToString("yyyy-MM-dd") + "')";