query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-03-04 : 04:50:02

select '1/1/1900' as created_date from tablename

but in table it shoud insert like 1900-1-1 12:00:00

what is the format

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 04:57:14

quote:
Originally posted by rajasekhar857

select '1/1/1900' as created_date from tablename

but in table it shoud insert like 1900-1-1 12:00:00

what is the format

As long as you use proper DATETIME datatype dont worry how dates are stored in the table. Internally date values are stored as two 4-bype integers. Let your front end application format the date as required

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-03-04 : 05:02:26

madhi i wan to insert in table like 1900-1-1 12:00:00 this.any format to do so from database side.
if ypu give select '1/1/1900' will give result as 1/1/1900 like wise i want that to be.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-04 : 05:15:13

if you want to insert dates using a string (it's better to do as Madhivanan suggested and at least pass the right data type from your app). Then stick to 1 of the two ISO standard strings.

These are:

Short Form


'yyyymmmdd'
-- Exanple '20100412' (2010 - APRIL - 12th @ MIDNIGHT - Start of the day)

And Long Form


'yyyy-mm-ddThh:mm:ss.ms'
-- Example '2010-04-12T12:04:12.100' (2010 - APRIL - 12th @ 12:04 PM and 12 seconds, 100 miliseconds

Then there can be no implicit conversion errors based on locale (UK / US for example).

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 05:28:39

quote:
Originally posted by rajasekhar857

madhi i wan to insert in table like 1900-1-1 12:00:00 this.any format to do so from database side.
if ypu give select '1/1/1900' will give result as 1/1/1900 like wise i want that to be.

Why do you want to store formatted dates?
Are you using any front end application?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources