none
how to set date field in sql server 2005 table

    Question

  • 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.
    Wednesday, October 24, 2007 11:25 AM

Answers

  • 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
    Wednesday, October 24, 2007 12:06 PM

All replies

  • 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
    Wednesday, October 24, 2007 12:06 PM
  • You can see responses to your duplicate post here.  Please avoid duplicate posting.  This post is now locked.

    • Proposed as answer by sbcollegeuser Friday, December 17, 2010 10:02 AM
    Wednesday, October 24, 2007 12:22 PM