How do I change SQL Server 2008 date format to UK (dd/mm/yyyy)?

Hi,

You can either change the date format of the entire db:
Use SET DATEFORMAT <format> to set the correct format for your database.
Valid parameters are mdy, dmy, ymd, ydm, myd, and dym. Can be either Unicode or double-byte character sets (DBCS) converted to Unicode.

If you do not use UK format only, this is not an option, of course.

When you convert your date, do you do the following?
select convert(varchar(10), mydate, 103) as 'MyDate' from MyTable

Hope this helped!

Thanks for your response.

I convert the date string as follows:
        quote_date = txtQuoteDate.Text
        quote_date = Convert.ToDateTime(quote_date)

I understand that I have to use "SET DATEFORMAT dmy"  for UK but can you please tell me where to place this command? Placing it in my code behind file produces the error "String or binary data would be truncated. The statement has been terminated."

Many Thanks, Don.

        Dim sqlConn As New SqlConnection(ConfigurationManager.AppSettings("LY-Conn"))
        Dim strSQL As String = "INSERT INTO risk_tbl (quote_ref, quote_date, consortium_code, prod_by, loa_code, contract_cc, broker_name, assured_name, orig_assured, owner_dets, renewal_type, comments, uw_name) VALUES ('" & quote_ref & "', '" & quote_date & "', '" & consortium_code & "', '" & prod_by & "', '" & loa_code & "', '" & contract_cc & "', '" & broker_name & "', '" & assured_name & "', '" & orig_assured & "', '" & owner_dets & "', '" & renewal_type & "', '" & comments & "', '" & uw_name & "')"
        Dim strSQL2 As String = "SET DATEFORMAT dmy"
        Dim sqlComm2 As New SqlCommand(strSQL2, sqlConn)
        Dim sqlComm As New SqlCommand(strSQL, sqlConn)
        sqlConn.Open()
        sqlComm2.ExecuteNonQuery()
        sqlComm.ExecuteNonQuery()
        sqlConn.Close()

Select allOpen in new window

the SQL command you are executing strSQL2,strSQL is trying to update a field with a larger value. for example 'dd/mm/yyyy' will take 10 characters but if you are using less then you will get this error.

give the query and table structure for more help

I suggest you to use Parameterized query.


http://www.dot4pro.com/aspdotnet/65-using-parameterized-query-to-avoid-datatime-problem.html

Regards
Bedanand

Have followed your suggestion, please see revised code, but am now getting this error:

String or binary data would be truncated. The statement has been terminated.

I am using asp.net (VB)

Do you have any other suggestions, please?

Protected Sub SaveData()
        Dim sqlConn As New SqlConnection(ConfigurationManager.AppSettings("LY-Conn"))
        Dim strSQL As String = "INSERT INTO risk_tbl (quote_ref, quote_date, consortium_code, prod_by, loa_code, contract_cc, broker_name, assured_name, orig_assured, owner_dets, renewal_type, comments, uw_name) Values (@quote_ref, @quote_date, @consortium_code, @prod_by, @loa_code, @contract_cc, @broker_name, @assured_name, @orig_assured, @owner_dets, @renewal_type, @comments, @uw_name)"
        Dim sqlComm As New SqlCommand(strSQL, sqlConn)
        With sqlComm.Parameters
            .AddWithValue("@quote_ref", txtRef.Text)
            .AddWithValue("@consortium_code", drpConsortium.SelectedValue)
            .AddWithValue("@quote_date", DateTime.Now)
            .AddWithValue("@prod_by", txtProdBy.Text)
            .AddWithValue("@orig_assured", txtOrigAssured.Text)
            .AddWithValue("@assured_name", txtAssuredName.Text)
            .AddWithValue("@uw_name", drpUWName.SelectedValue)
            .AddWithValue("@broker_name", drpBroker.SelectedValue)
            .AddWithValue("@loa_code", drpLOACode.SelectedValue)
            .AddWithValue("@contract_cc", drpContractCert.SelectedValue)
            .AddWithValue("@owner_dets", txtOwnerDets.Text)
            .AddWithValue("@renewal_type", drpRenewalType.SelectedValue)
            .AddWithValue("@comments", txtComments.Text)
        End With
        sqlConn.Open()
        sqlComm.ExecuteNonQuery()
        sqlConn.Close()
    End Sub

Select allOpen in new window

the datatype in the database is a datetime. It really doesn't care about dmy or mdy - that is more of a presentation thing, and a default format for dates if you do not explicitly manage dates. True, the set DATEFORMAT does tell the server the default ordinal position of day and month, but best not to rely on that and take control of your dates.

When ever there is a date being represented as a string, or, represented based on a client regional setting, then you really should be telling the database how it is being presented. The 'correct' way is to use the convert function with a style code. On the front end, you need to use a date construct to capture the date then you have a few choices... Best is to format that captured field into a "known" date format and then use that along with the convert to make sure SQL understands the date string being passed to it.

There is a dateformat (as a string) that will always be implicitly converted to datetime - that is yyyymmdd (and in 2008 also yyyy-mm-dd, along with a few other date datatypes like date and time).  There is another but is dependant on language of the database - and that is dd MMM yyyy  but spanish (for example) spells May differently to English, so that is why it is dependant on language. When I know it is all english, it is my preferred format (that is style code 106).

So, if you do not want to use a convert function, then pass the date as a yyyymmdd string and it will work, simply format your date accordingly in your code.

However, if you want to use dd/mm/yyyy then you have to tell SQL how to read that date. To do that, use the convert function.


e.g. convert(datetime, my_ddmmyyyy_field, 103)  -- where 103 style = dd/mm/yyyy and 101 = mm/dd/yyyy and 106 = dd MMM yyyy and 112 = yyyymmdd and 120 = yyyy-mm-dd hh:mm:ss etc...


So in your original Insert string :

        Dim strSQL As String = "INSERT INTO risk_tbl (quote_ref, quote_date, consortium_code, prod_by, loa_code, contract_cc, broker_name, assured_name, orig_assured, owner_dets, renewal_type, comments, uw_name) " &
                                             "VALUES ('" & quote_ref & "', convert(datetime,'" & quote_date & "',103), '" & consortium_code & "', '" & prod_by & "', '" & loa_code & "', '" & contract_cc & "', '" & broker_name & "', '" & assured_name & "', '" & orig_assured & "', '" & owner_dets & "', '" & renewal_type & "', '" & comments & "', '" & uw_name & "')"
 
Or in your second Insert string using params  (ie .AddWithValue("@quote_date", DateTime.Now) ) :

if you were to (doing it 'long hand') create a new string variable :

Dim rightNow as DateTime = DateTime.Now
Dim sdate as String
sdate = rightNow.ToString("yyyyMMdd")

or to repserve sDate for the 'session' if doing a few things:

dim sDate as string = Format(DateTime.Now, "yyyyMMdd").ToString

or use directly in place of datetime.now :

DateTime.Now.ToString("yyyyMMdd");

Have followed your suggestion, please see revised code, but am now getting this error:

String or binary data would be truncated. The statement has been terminated.


The problem here can be if you are trying to insert data more than the column size supports. This can be on the other columns of varchar. check size on datatable for all columns.

it looks like the text boxes are accepting more than allowed size of data. you can restrict the same and accept only specified length strings. check the length of text boxes.

Dear All,

Many thanks for your responses:

1.) Decided to use a parameterized query as per the attached snippet, as I believe it is more secure.
2.) Have set the MaxLength property of all the text boxes on the form to their appropriate values as per SQL-Server-2008 table definition.
3.) Prior to saving I change the date string from "dd/MM/yy" to "yyyy-MM-dd" - i.e. 10 chars width.
4.) In the code section where I add the parameters I define the data types with their appropriate lengths  i.e. SqlDbType.VarChar = 50).Value = txtName.Text.

In spite of all this re-writing I still get the following error: I believe it is telling me that a table field is too small to accept the input, but I assure you I have triple checked the variable lengths.

"String or binary data would be truncated. The statement has been terminated."

I cannot think of anything else I need to do to make this INSERT routine work.

Suggestions, guidance and thoughts would be most welcome - please help.

Many Thanks.

Don.

    Protected Sub SaveData()
        Dim quote_date As String = txtQuoteDate.Text.Trim ' captured on form as dd/MM/yyyy
        quote_date = Right(quote_date, 4) & "-" & Mid(quote_date, 4, 2) & "-" & Left(quote_date, 2)
        'quote_date value =  "yyyy-MM-dd" and passes the IsDate test
        Dim sqlConn As New SqlConnection(ConfigurationManager.AppSettings("LY-Conn"))
        Dim strSQL As String = "INSERT INTO risk_tbl (quote_ref, quote_date, consortium_code, prod_by, loa_code, contract_cc, broker_name, assured_name, orig_assured, owner_dets, renewal_type, comments, uw_name) Values (@quote_ref, @quote_date, @consortium_code, @prod_by, @loa_code, @contract_cc, @broker_name, @assured_name, @orig_assured, @owner_dets, @renewal_type, @comments, @uw_name)"
        Dim sqlComm As New SqlCommand(strSQL, sqlConn)
        With sqlComm.Parameters
            .AddWithValue("@quote_ref", SqlDbType.VarChar = 1).Value = txtRef.Text
            .AddWithValue("@consortium_code", SqlDbType.VarChar = 50).Value = drpConsortium.SelectedValue
            .AddWithValue("@quote_date", SqlDbType.DateTime = 10).Value = quote_date
            .AddWithValue("@prod_by", SqlDbType.VarChar = 50).Value = txtProdBy.Text
            .AddWithValue("@orig_assured", SqlDbType.VarChar = 250).Value = txtOrigAssured.Text
            .AddWithValue("@assured_name", SqlDbType.VarChar = 150).Value = txtAssuredName.Text
            .AddWithValue("@uw_name", SqlDbType.VarChar = 100).Value = drpUWName.SelectedValue
            .AddWithValue("@broker_name", SqlDbType.VarChar = 50).Value = drpBroker.SelectedValue
            .AddWithValue("@loa_code", SqlDbType.VarChar = 1).Value = drpLOACode.SelectedValue
            .AddWithValue("@contract_cc", SqlDbType.VarChar = 1).Value = drpContractCert.SelectedValue
            .AddWithValue("@owner_dets", SqlDbType.VarChar = 50).Value = txtOwnerDets.Text
            .AddWithValue("@renewal_type", SqlDbType.VarChar = 1).Value = drpRenewalType.SelectedValue
            .AddWithValue("@comments", SqlDbType.VarChar).Value = txtComments.Text.Trim
        End With
        sqlConn.Open()
        sqlComm.ExecuteNonQuery()
        sqlConn.Close()
    End Sub

Select allOpen in new window

Mark, Thank you so much for your patience and advice, I've learned a great deal from this particular challenge - it will all come in handy for the rest of my project. Best Regards, Don.

Thanks Don, happy to help... and trust you already caught the missing size for @comments (it will truncate)...