select convert(datetime,''15/11/2006'',101) does work

Question

select convert(datetime,''15/11/2006'',101) does work

John Burchel

Hall of Fame

Points: 3611
More actions
December 21, 2006 at 6:31 am

#114981

Does anyone know why: select convert(datetime,'15/11/2006',101)
wouldnt work, I have all the dates in my table in this format, and cannot change them, but I can only convert them to use in other applications.
Any help please ?
Kindest Regards,
John Burchel (Trainee Developer)

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

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

DaveItz SSCrazy Points: 2146 More actions · Answer 1

Taken from 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 datetime data in the tables is not really stored in any readily readable format. Date formats are what you apply when you extract the data from the table for display (or other) purposes. For example: select convert(varchar, MyDatetimeField, 101) From MyTable.

If you want to store them in the table SQL Server will automatically understand the format 'yyyymmdd'.

Insert Into MyTable(MyDateTimeField) select '20061115'

Otherwise it will assume you are using the current DATEFORMAT settings and will attempt a conversion from the set format.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Because the date format most likely is MM/DD/YYYY

RUN THIS (scope is for the connection only)

SET DATEFORMAT DMY

and the convert should work.

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 3

Hi Jhon ,

select convert(datetime,'15/11/2006',101)

This statement will not work.

B'coz Sql server by default take the date format in (mon/dd/yyyy).

If you use this satement like :

select convert(datetime,'11/15/2006')

It will work fine..

And one more thing i want to share with you that if you are converting a varchar value to a datetime then you do not need to pass the style parameter like (101,102,121 etc.) these parameters are used to convert a date value to a varchar type..

Thnks..

If you have any problem you can discuss it to me at :

mailgupta_amit@yahoo.com

Amit Gupta.,.

Sql Server MVP.

/* Problem makes man perfect... */

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Because you have instructed SQL Server to convert using style 101, which is mm/dd/yyyy. That is, you are trying to create a date with month 15.

Use

select convert(datetime,'15/11/2006',103)

Vladan SSC-Insane Points: 22072 More actions · Answer 5

I almost posted the same... but then ran a test and realized that this really isn't the problem. Using a style (like 101 or 103) with CONVERT overrides dateformat setting. SO the problem is in the style itself and changing the style to 103 alone should work, independent on DATEFORMAT or any other settings. In fact, changing DATEFORMAT does not help at all...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Thanx for the pre-feedback, John won't have to correct out small error now .

Mauricio Morales S Ten Centuries Points: 1300 More actions · Answer 7

The problem is that yu are instructing to SQL to Apply Style to one DateTime DataType.

The Styles only apply for Varchar(n) DataType see the Help.