chenyuhao88
asked on
Format of Datetime suddenly stop working
Dear All,
I have a SQL query in my dot net application, it used to work fine. The QUERY is:
Select * from OrderHeader where OrderType = 's'
and OrderDate >= '15/01/2006 12:01:00 AM'
Order by CustomerBranchID, OrderDate
The datetime "'15/01/2006 12:01:00 AM'" comes from "datetime" object in my dot net application. It used to work fine. However after last time I re-install my vista, vs and sql server, it stopped working. The error is:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
If I change the date from "'15/01/2006 12:01:00 AM'" to "'10/01/2006 12:01:00 AM'", then it works. It looks like SQL treat my current datetime format as "MM/DD/YYY", rather than "DD/MM/YYY".
My question is: if I do not want to change my code a lot, is it possible just to change the configuration of my SQL server 2005?
Thanks heaps!
I have a SQL query in my dot net application, it used to work fine. The QUERY is:
Select * from OrderHeader where OrderType = 's'
and OrderDate >= '15/01/2006 12:01:00 AM'
Order by CustomerBranchID, OrderDate
The datetime "'15/01/2006 12:01:00 AM'" comes from "datetime" object in my dot net application. It used to work fine. However after last time I re-install my vista, vs and sql server, it stopped working. The error is:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
If I change the date from "'15/01/2006 12:01:00 AM'" to "'10/01/2006 12:01:00 AM'", then it works. It looks like SQL treat my current datetime format as "MM/DD/YYY", rather than "DD/MM/YYY".
My question is: if I do not want to change my code a lot, is it possible just to change the configuration of my SQL server 2005?
Thanks heaps!
Check the date format on Vista. I think the format of the date on vista has changed to mm/dd/yyyy. Change it back to dd/mm/yyyy, it should work!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the first one i mentioned does not supoprt time, only date (yyyymmdd)
so if it's not good for you, use the second option
so if it's not good for you, use the second option
ASKER
ASKER
momi_sabag:,
I will try your 1st solution if others are not working;
How can I set my default login language in VISTA?
Thanks again!
I will try your 1st solution if others are not working;
How can I set my default login language in VISTA?
Thanks again!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jpaulino,
Your parameters method could be another good solution.
At this moment, I still want to know why after re-install the OS and software, it stopped working.
Actually I asked the user to enter date with the format "YYYY/MM/DD". In this case I could avoid using Date.ParseExact(txtDate.Te
Your parameters method could be another good solution.
At this moment, I still want to know why after re-install the OS and software, it stopped working.
Actually I asked the user to enter date with the format "YYYY/MM/DD". In this case I could avoid using Date.ParseExact(txtDate.Te
How can I set my default login language in VISTA?
your are setting the default login language for the sql server login, not the vista login
just right click the login name in the management studio,
choose properties and you can do it from there
your are setting the default login language for the sql server login, not the vista login
just right click the login name in the management studio,
choose properties and you can do it from there
Check out your Regional Settings in your Control Panel for Date and Time Formats. If you change that, it will be resolved. Your Dot Net application takes time in the format as mentioned by your regional settings in your Windows machine.
Can you show the SQL statement that you to insert the values and how do you format the dates ?
If you use a datetimepicker you can do:
"INSERT INTO myTable (a,b,c) VALUES (1,2, '#" & Me.DateTimePicker1.Value.T
More or less like this
"INSERT INTO myTable (a,b,c) VALUES (1,2, '#" & Me.DateTimePicker1.Value.T
More or less like this
ASKER
jpaulino,
There is nothing special. Like what I posted, the SQL query =
"Select * from OrderHeader where OrderType = 's' and OrderDate >= ' " & Date.ParseExact(textDate.t
Order by CustomerBranchID, OrderDate"
rrjegan17,
I checked and that's my default settings;
momi_sabag,
I go to: security --> login --> right click "sa" --> properties, but there is no such choice for date format. Is it possible I use Management Studio Express Edition?
There is nothing special. Like what I posted, the SQL query =
"Select * from OrderHeader where OrderType = 's' and OrderDate >= ' " & Date.ParseExact(textDate.t
Order by CustomerBranchID, OrderDate"
rrjegan17,
I checked and that's my default settings;
momi_sabag,
I go to: security --> login --> right click "sa" --> properties, but there is no such choice for date format. Is it possible I use Management Studio Express Edition?
Didn't you saw my previous comment ?
"mm" represents minute and not month. "MM" is the correct format for month.
You can use:
textDate.text.trim.ToStrin
"mm" represents minute and not month. "MM" is the correct format for month.
You can use:
textDate.text.trim.ToStrin
"Select * from OrderHeader where OrderType = 's' and OrderDate >= '" & textDate.text.trim.ToString("yyyy/MM/dd") & "' Order by CustomerBranchID, OrderDate"
OR
"Select * from OrderHeader where OrderType = 's' and OrderDate >= '#" & textDate.text.trim.ToString("yyyy/MM/dd") & "#' Order by CustomerBranchID, OrderDate"
ASKER
jpaulino,
Many thanks. I will try tomorrow and let you know the result.
BTW, what's the different between normal text "2009/01/15" and ("2009/01/15").ToString("y
Many thanks. I will try tomorrow and let you know the result.
BTW, what's the different between normal text "2009/01/15" and ("2009/01/15").ToString("y
If you do this:
Dim textDate As String = "2009/02/13"
Debug.WriteLine(Date.Parse
You get and error because you cannot parse the string. You should validate and use it this way:
Dim dt As DateTime
Dim textDate As String = "2009/02/13"
If Date.TryParse(textDate, dt) Then
' now you have a valid date
Debug.WriteLine(dt.ToStrin
End If
Dim textDate As String = "2009/02/13"
Debug.WriteLine(Date.Parse
You get and error because you cannot parse the string. You should validate and use it this way:
Dim dt As DateTime
Dim textDate As String = "2009/02/13"
If Date.TryParse(textDate, dt) Then
' now you have a valid date
Debug.WriteLine(dt.ToStrin
End If
You can also include and Else in the TryParse() method and show to the user an error message indicating that the date is not valid.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> only be used with Access and not with SQL server
I don't know if it access or SQL ... :)<!-- richText -->
I don't know if it access or SQL ... :)<!-- richText -->
>>I don't know if it access or SQL ... :)
see the last phrase of the question.
see the last phrase of the question.
OMG ... I miss that :)
I even showed some examples of using parameters in Access and not SQL!!!
I even showed some examples of using parameters in Access and not SQL!!!
ASKER
Thanks all. It works fine now. I just use the universal format yyyy/mm/dd.