SmallDateTime

Question

SmallDateTime

BuilderBits

SSCertifiable

Points: 6071
More actions
May 19, 2006 at 11:02 am

#113035

i am trying to insert the date in the format of mm/dd/yyyy.. But i keep getting the time in the column also
The field type is smalldatetime.. Can someone please tell me the correct way to insert the month, day, and year?

thank you

Erik
Dam again!

Viewing 4 posts - 1 through 3 (of 3 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

You can't avoid getting a time component in a smalldatetime column - it's designed to store date & time, so that's what it stores.

Declare @TestDate As Smalldatetime

Select @TestDate = '19 May 2006'

Select @TestDate

Result:

2006-05-19 00:00:00

If you want the time portion removed, that's a 'presentation' issue, which you do by either converting to a varchar in a certain format, or let your front-end app or report strip it off for presentation purposes:

-- Convert code 106 - 'dd mmm yyyy'

Select Convert(varchar, @TestDate, 106)

Result:

19 May 2006

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Also, the SMALLDATETIME and DATETIME data types DO NOT store the date in any 'normal' format.

This is from the BOL:

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

----------

Lastly, how sure are you that the proper information is getting to your database? Inserting the value: 01/02/2006 does not necessarily mean SQL Server 'sees' that as Jan 2, 2006. It could be 1 Feb 2006.

When entering dates either use yyyy-mm-dd or use SET DATEFORMAT. (SET DATEFORMAT MDY).

-SQLBill

BuilderBits SSCertifiable Points: 6071 More actions · Answer 3

Thank you for the replys and help

was very helpful..

erik

Dam again!