how to insert date in sql server 2008

Question

0.00/5 (No votes)

See more:

i am using the below table

SQL

create table newemprequest(jobid
      ,jobcategory
      ,[skill]
      ,[opendate] date
      ,[enddate] date
      ,[experiencerequired]
      ,[numberofvacancies]
      ,[jobdescription])

i am inserting below query

SQL

insert into newemprequest values(1,'canesection','canecutter',10-10-13,20-10-13,2,10,'jfkadsklflskfd')

I got following error :
Operand type clash: int is incompatible with date any one help me how to insert date in sql server 2008.

Posted 25-Oct-13 5:00am

balu katuri

Updated 25-Oct-13 5:08am

gvprabu

v2

Add a Solution

4 solutions

Solution 1

Try:

SQL

insert into newemprequest values(1,'canesection','canecutter','10-10-2013','20-10-2013',2,10,'jfkadsklflskfd')

But be aware that SQL defaults to ISO date format: yyyy-MM-dd and it is a very good idea to use it if at all possible...

Posted 25-Oct-13 5:11am

OriginalGriff

Comments

gvprabu 25-Oct-13 11:23am

I am having one doubt.... If we pass "20-10-2013" then also we will get error right
DECLARE @Test DATETIME
SET @Test ='20-10-2013'
SELECT @Test

Error
Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

OriginalGriff 25-Oct-13 11:36am

That's because it defaults to ISO yyyy-MM-dd, and unless you provide it in that format it just "tries it's best". In this case, because MS is American, it tries to evaluate the date as 10th day of the 20th Month of 2013 and fails.

Assuming that your example was just that - an example - change to ISO format and it'sll work.

But by preference, if you are inserting from an application, pass a DateTime object via a parameterised query instead of supplying a string , and it will not longer matter what format you pass. Mind you, if you were inserting from an application, you would already be using all parameterised queries to avoid SQL Injection attacks anyway, wouldn't you?

gvprabu 25-Oct-13 11:43am

thanks.... a lot.

OriginalGriff 25-Oct-13 12:11pm

You're welcome!

Solution 3

When I use any date value to table i always use yyyy-MM-dd ISO format.

Insert into Table(DateField) values ('2013-12-01');

If you follow this pattern then it will work other database too.

Posted 25-Oct-13 7:11am

S. M. Ahasan Habib

Solution 4

All the above A are ignoring a basic tenant here in Q&A, and that's that the OP (Q) can only tell the A what he knows about what he's trying to do. So:

create table newemprequest(jobid
      ,jobcategory
      ,[skill]
      ,[opendate] date
      ,[enddate] date
      ,[experiencerequired]
      ,[numberofvacancies]
      ,[jobdescription])

This is not right. And forget about all A quibble over "default". Not important.
Do some help file searches on datetime, there are examples; that would be good advice. But here's what a table creation involves. Table names, column names, data types, data sizes. Ambiguity is the enemy. A schema too.

USE [database]
GO
CREATE SCHEMA [schema]

CREATE TABLE [schema].[newemprequest](
   [jobid][int],
      [jobcategory][nvarchar](25),
         [skill][nvarchar](26),
             [opendate][nvarchar](666),
                 [enddate][nvarchar](666),
                     [experiencerequired][nvarchar](11),
                         [numberofvacancies][int],
                              [jobdescription][nvarchar](251)
          )

What's wrong with [nvarchar](666) as a date/time data type? Nothing.

Posted 25-Oct-13 7:29am

RedDk

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-10-25T05:12:00

Hi,

Its simple .... check any INSERT Statement Sample...

SQL

INSERT INTO newemprequest(jobid, jobcategory, skill, opendate, enddate, experiencerequired, numberofvacancies, jobdescription)
VALUES(1,'canesection','canecutter', '2013-10-10', '2013-10-20',2,10,'jfkadsklflskfd')

You have to follow some standards.... like give all column names and date values in YYYY-MM-DD Format ext... check the below links
INSERT Examples (Transact-SQL)[^]
Simple SQL Server INSERT statement[^]
SQL INSERT INTO Statement[^]
SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor[^]

Regards,
GVPrabu