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
dotnet0824Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

udaya kumar laligondlaTechnical LeadCommented:
always store data using YYYY/MM/DD as it is right practice. after reading format the same to any format you want
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
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'
0
pcelbaCommented:
But I agree, date stored in 'YYYYMMDD' character format can easily be interpreted, sorted, and converted as uday said.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

BrandonGalderisiCommented:
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.
0
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)
E.g
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.
0
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
0
ingfragaCommented:
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.
0
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"
0
pcelbaCommented:
If the CustDate is datetime them you may simply convert the passed string:

CONVERT(datetime, @strdate, 103)

0
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.

0
pcelbaCommented:
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.
0
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 dd.mm.yyyy or mm.dd.yyyyy. I am totally lost regarding this though.
0
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.

0
pcelbaCommented:
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").
0
dportasCommented:
DATETIME doesn't have a format but you can use CONVERT to convert a string. Example:

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

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.
0
BrandonGalderisiCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.