datetime store method

Question

datetime store method

balaji.ganga

SSCrazy

Points: 2280
More actions
May 12, 2010 at 11:47 pm

#74604

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Renuka__ Hall of Fame Points: 3656 More actions · Answer 1

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]

balaji.ganga SSCrazy Points: 2280 More actions · Answer 2

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

IMO, this link is required reading

http://www.karaszi.com/SQLServer/info_datetime.asp

Clear Sky SQL
My Blog[/url]

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

also nice to read: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx