insert date in sql server

declare @print_date varchar(30)
set @print_date = '24/04/2009'

Insert into Customer(strDate) values(@print_date) --> I want date to be stored as 04/24/2009
mm/dd/yyyy format

Please let me know how it can be converted to be inserted in that table
Who is Participating?
udaya kumar laligondlaConnect With a Mentor Technical LeadCommented:
always store data using YYYY/MM/DD as it is right practice. after reading format the same to any format you want
What is the data type of strDate column?  Datetime or char?

If it is char or varchar, simply change the set @print_date to:
set @print_date = '04/24/2009'
pcelbaConnect With a Mentor Commented:
But I agree, date stored in 'YYYYMMDD' character format can easily be interpreted, sorted, and converted as uday said.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

BrandonGalderisiConnect With a Mentor Commented:
No uday... always store DATETIME values in DATETIME columns.  SQL Server ACTUALLY stores the value in a numeric field with the whole number representing the DATE and the decimal value representing the TIME.  

If you store datetime values in varchar fields, you will be back when people start putting 2009-01-01, 2009/01/01, 01-01-2009, 12/31/2009, 31/12/2009 and anything else you can think of.
Chris MConsulting - Technology ServicesCommented:
If the "strDate" column is of data type "datetime", then it does not matter how you store it, it will always be stored using the standard datetime format (like 2009-04-22 00:00:00.000) irrespective of how it's inserted.
If it's to be mm/dd/yyyy, then use  the query:
select CONVERT(CHAR(10), @print_date, 101)
Insert into Customer(strDate) values(CONVERT(CHAR(10), @print_date, 101);
-- NB: @print_date should be in datetime date format otherwise if in varchar format, then the query will look like:
Insert into Customer(strDate) values(CONVERT(CHAR(10), convert(datetime,@print_date), 101);  
Good day, God bless.
Chris MConsulting - Technology ServicesCommented:
I forgot to say that the queries i specified above are to be used in case the strDate column is a varchar column
Good day
Sql server, saves the date in an special numeric format, the visualization depends on the language/country, this can be changes in your operating system, or the sql server configuration options, or even in you .net aplicacation, just choose the datetime CultureInfo.
dotnet0824Author Commented:
Points increased
sorry that i dint mention the dataType of the table which takes in the date

Table  Customer
CustomerID  int
CustDate      datetime

I cant change the Date which is going to sql server. I mean its currently in the format of   24/04/2009 (ie dd/mm/yyyy and its being passed as Varchar(30)). I tried inserting the same in the stored proc but it throws error
if I send lets say "04/24/2009" then insertion succceeds .. So I have to change the format in stored proc to mm.dd.yyyy  hope its clear.

input type :  @strdate Varchar(30)  value going to stored proc is  "24/04/2009"
If the CustDate is datetime them you may simply convert the passed string:

CONVERT(datetime, @strdate, 103)

dotnet0824Author Commented:
this works it inserts  2009--04-24

declare @strdt varchar(30)
SET @strdt = '24/04/2009'

Select CONVERT(datetime, @strdt, 103)

But when i say  Select CONVERT(datetime, @strdt, 101) --- Convert string to MM.DD.YYYY it throws error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Yes, the 101 conversion type requires mm/dd/yyyy format which is not your case.

Even when you are sure the date is in dd/mm/yyyy format you should validate it before conversion or catch the conversion error and return some error information to the calling program.
dotnet0824Author Commented:
how is that we can make sure insertion goes on fine with sql server
lets say client app Regional settings is dd/mm/yyyy and sql server on different box with  mm/dd/yyyy(lets assume we dont know what the settings of date is configured in sql server). How would be make sure  to pass or mm.dd.yyyyy. I am totally lost regarding this though.
dotnet0824Author Commented:
Actually why does this fail. Isnt it that it has to convert to mm.dd.yyy format

But when i say  Select CONVERT(datetime, @strdt, 101) --- Convert string to MM.DD.YYYY it throws error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

To allow date entry by different clients around the world and process it on central server in e.g. US must follow some rules at client side.

If you obtain just some text you cannot decide about its processing without formatting details. So the input should follow given rules (dd/mm/yyyy) or it should contain date format as the second parameter.

Even if the passed date is processed as valid you cannot ensure the correct value (e.g. "03/08/2009").
DATETIME doesn't have a format but you can use CONVERT to convert a string. Example:

SELECT CONVERT(datetime, '24/04/2009', 103) dt;

2009-04-24 00:00:00.000

I agree with the other answers. You should change the datatype of the input value to a date instead of a string. That way you won't have to worry about formatting or internationalisation.
Let me ask this.  
Is this value coming from a date control in .Net?  I'm guessing based upon your username.  If so, try using .value instead of .text to pull the datetime value.  This should then be passed into a DATETIME parameter and not a varchar parameter.  The problem with doing any specific format code is that you are trying to hack a hack with another hack.  Just pass a datetime value and all will be well.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.