Solved

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

Posted on 2009-03-29
12
2,540 Views
Last Modified: 2012-06-27
Hi, I'm Classic ASP trained now using Visual Studio 2008, and having a problem understanding how to make SQL Server 2008 accept dates in the UK format - the default value or binding for a date field in my table is '12/31/1999' - when I try to change this in SQL Server to UK format I get a Validation error warning. The date I want to Insert is a string in the format "dd/mm/yyyy" which I convert to date before Inserting (pls see code snippet) - I continually get "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" error.
I have inserted <globalization culture="en-GB" uiCulture="en-GB" /> in the System.Web section of Webconfig and attach a code snippet for convenience.
Have spent many hours researching this on the internet without success. Please help me,
Regards, Don.
0
Comment
Question by:DEAndrews
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 4

Expert Comment

by:amazingwolf1
ID: 24012880
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!
0
 

Author Comment

by:DEAndrews
ID: 24013079
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()

Open in new window

0
 
LVL 12

Expert Comment

by:udayakumarlm
ID: 24013173
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
0
 
LVL 8

Expert Comment

by:bedanand
ID: 24013391
0
 

Author Comment

by:DEAndrews
ID: 24013549
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

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24015067
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");



0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:bedanand
ID: 24015600
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.

0
 
LVL 12

Expert Comment

by:udayakumarlm
ID: 24015955
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.
0
 

Author Comment

by:DEAndrews
ID: 24017390
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

Open in new window

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 24017465
In SSMS, right click on the table, script table as, create, new query window.

Then copy and paste the results. Could be varchar (1 byte per char) versus nvarchar (2 bytes per char) for example...

also, if you are formatting yyy-mm-dd then pass as sqldbtype.varchar=10

It will implicitly convert to a datetime - see below example

declare @d datetime
set @d = '2009-03-30'
select convert(datetime,'2009-03-30'), @d
0
 

Author Closing Comment

by:DEAndrews
ID: 31564026
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24020832
Thanks Don, happy to help... and trust you already caught the missing size for @comments (it will truncate)...
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now