?
Solved

insert date in sql server

Posted on 2009-04-22
17
Medium Priority
?
322 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:dotnet0824
  • 5
  • 4
  • 2
  • +4
16 Comments
 
LVL 12

Accepted Solution

by:
udaya kumar laligondla earned 400 total points
ID: 24206819
always store data using YYYY/MM/DD as it is right practice. after reading format the same to any format you want
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24206828
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
 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 400 total points
ID: 24206853
But I agree, date stored in 'YYYYMMDD' character format can easily be interpreted, sorted, and converted as uday said.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 400 total points
ID: 24207303
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
 
LVL 12

Expert Comment

by:Chris M
ID: 24207370
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
 
LVL 12

Expert Comment

by:Chris M
ID: 24207406
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
 
LVL 1

Expert Comment

by:ingfraga
ID: 24207571
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
 

Author Comment

by:dotnet0824
ID: 24208068
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
 
LVL 43

Expert Comment

by:pcelba
ID: 24208134
If the CustDate is datetime them you may simply convert the passed string:

CONVERT(datetime, @strdate, 103)

0
 

Author Comment

by:dotnet0824
ID: 24208197
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
 
LVL 43

Expert Comment

by:pcelba
ID: 24208256
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
 

Author Comment

by:dotnet0824
ID: 24208259
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
 

Author Comment

by:dotnet0824
ID: 24208277
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
 
LVL 43

Expert Comment

by:pcelba
ID: 24208371
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
 
LVL 22

Expert Comment

by:dportas
ID: 24208848
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24211676
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question