Changing to British Time Settings in SQL 2000

[VBXL]

In SQL Server 2000,the time is in American format. How do i change it to British Format.

I have tried DATEFORMAT dmy
and tried changing it in the Options.

Any other Ideas

Cheers

GazC

 

[rajeevnandanmishra]

Hi GazC,
I apolozise but i didn't understand that the time is in American format. How do i change it to British Format.
Though if you are talking about the date values that can be done by using convert(char(10),myDateField,103).

If you require something else please let me know.

 

[tlbroadbent]

Dates and times are not stored in American format, British format or any other national format. Dates and times are stored as numbers - pure and simple. SQL provides various formats or styles for displaying dates and times. DATEFORMAT only affects how SQL interprets date strings as they are entered.

From SQL BOL: "SET DATEFORMAT: Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data."

Use CONVERT(char(??),dateval,style) to display dates, times or date/time values in the style you want. Check the 'Cast and Convert' topic in SQL BOL for a list of style codes. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Corinne]

rajeevnandanmishra,

I'm having a problem converting dates. This is the line I wrote to convert but doesn't work correctly.

"DOB = CONVERT(datetime, txtDOB, 102), "

Can you explain your convert(char(10),mydatefield,103) to me. I don't understand whay the char(10) is for and also the difference between the 102 and the 103.

Thanks for any help you can give me.

Corinne

 

[tlbroadbent]

Converting to different date formats or styles requires converting from a datetime value (or column) to a chararcter coulmn. I use char(10) frequently because I want to see dates in 'mm/dd/yyyy' format which is 10 characters long.

102 and 103 are two of the styles available for converting datetime columns to character strings or columns. Check the following link for a list of styles.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Corinne]

My data type in the table is smalldatetime and everytime data is saved to my table, if the date field is empty then 1/1/1900 is saved in the field. Why does it seem to default to 1/1/1900? Should I change the data type to datetime?

Thanks,
Corinne

 

[rajeevnandanmishra]

Hi Corinne,
I had used char(10) to convert the date into character.

102 returns you the date in yyyy.mm.dd format while
103 returns you the date in dd/mm/yyyy format.

And DOB = CONVERT(datetime, txtDOB, 102) will generate an error if the date value in the variable txtDOB is not in yyyy.mm.dd format because in that case it is difficult for sql to convert the date values.

Hope i had explained what you require.

 

[tlbroadbent]

As mentioned, datetime fields are numbers*. Zero (0) corresponds to the date 1/1/1900. That is why 1/1/1900 shows up in a default display when only a time has been stored in the datetime field.

*From SQL 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." Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Corinne]

Thank you all for your help with this. With the direction you gave me ....problem solved.

Thanks again,
Corinne