how to set date field in sql server 2005 table

SQL Server Express

sekar_r24


hi friends

i wish to set a date data type to a column "Date". when i use datetime format, the values is inserted in the U.S. date & time format. i want the values to be in the british english format. i.e dd/mm/yyyy only and does not require time value. can any one help me to insert the date values in the british english format


regards..,

sekar.

Top

Re: how to set date field in sql server 2005 table

Kent Waldrop Oc07


Sekar:

Look up DATE AND TIME and related articles in books online to begin to get an understanding of the DATETIME datatype. Understand that there is no such datatype in SQL Server for DATE -- the datatype is DATETIME. As such, you cannot store date only in this datatype. The next thing that you need to give a look to is SET DATEFORMAT in books online. This will show you how to set your format to the DMY format you indicated.

Code Block
SET DATEFORMAT DMY

Normal practice is to store a time of 12AM or 00:00:00 when the intention is to store date only in a datetime column. This is in fact what the database does by default. If you store a datetime of 18/2/7 in the database it will store this date and assume a time of 12AM. The way that this gets handled is by properly formatting the datetime columns into varchar strings when displaying the datetime. This is accomplished by using the CONVERT function. To select only the date portion from a column named "yourDate" in a table named "yourTable" the select might look something like:

Code Block

select convert(varchar(10), yourDate, 103)

from yourTable

Take note of the "103" in the convert function. That is the format specification to format datetime columns into a date format of DD/MM/YY.

Give a look at the following articles in books online:

  • DATE AND TIME
  • SET DATEFORMAT
  • CONVERT



Top

Re: how to set date field in sql server 2005 table

Kent Waldrop Oc07

You can see responses to your duplicate post here. Please avoid duplicate posting. This post is now locked.



Top