May 12, 2010 at 11:47 pm
Dear All,
I have one table. table name is abc.. it consists of two column id, datetime, datatype is int,datetime only. My quest is while i'm inserting a row.. it would be stored in the following format
1, '2010-05-13 11:16:15.000'
But i want to store those values in these format like dd-MM-yyyy hh:mm:ss format.
1, 13-05-2010 11:16:15.000'
kindly let me know.. if any body having the solution.
May 13, 2010 at 4:53 am
Hi,
I am not sure if we can use CONVERT function in INSERT statement; but you can use CONVERT while retrieving Datetime data in the format you want. Check the link below for various formats.
http://www.sql-server-helper.com/tips/date-formats.aspx
Thank you
[font="Verdana"]Renuka__[/font]
May 14, 2010 at 12:24 am
hi renuka..
create table abc(id int identity(1,1),name varchar(50),dob datetime)
insert into abc values('Balaji', convert(nvarchar,getdate()-10974,103))
it store the data in dd-mm-yyyy format. but its not a datetime format.
Thanks & Regards
Balaji.G
May 14, 2010 at 12:32 am
I think what I'm seeing is a fundamental misunderstanding of what happens when you store data in SQL. You cannot control the format it is stored as, you put a date/time value in and it stores them ALL the same, regardless of regional settings. If you want a specific format you have to do a CONVERT or CAST when you read the field out. The same is true for all other datatypes as well, for example you want an integer to contain commas at the thousands point (1,000), you can't insert the data that way and expect it to come out the same, it will ALWAYS be stored as 1000 in whatever the format is internally..
CEWII
May 14, 2010 at 12:35 am
Don't fool yourself nor the engine and define the column using data type datetime !
Rule no 1: tell the system what you know !
Rule no 2: use the correct data type !
Sooner or later someone will write queries that will convert your int content to date and/or time trying to filter e.g. day of week, ....
With the date and time data types come a bunch of very useful date and time related functions !
Avoid conversions as much as you can !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 14, 2010 at 1:31 am
IMO, this link is required reading
May 18, 2010 at 5:29 am
also nice to read: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 19, 2010 at 8:45 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply