[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

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!



 



 
0
chenyuhao88
Asked:
chenyuhao88
  • 8
  • 6
  • 3
  • +4
4 Solutions
 
dhananjayadCommented:
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!
0
 
momi_sabagCommented:
you have 2 options to do it through sql server:
1) you can use a universal date format which will always be correct and it is
yyyymmddhhmmssmmmmmm
2) you can set the default language of your login and that will allow you to have a consistent datetime format reagardless the one window uses
0
 
momi_sabagCommented:
the first one i mentioned does not supoprt time, only date (yyyymmdd)
so if it's not good for you, use the second option
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
chenyuhao88Author Commented:
dhananjayad,

my default setting is DD/MM/YYY....
Q1.jpg
0
 
chenyuhao88Author Commented:
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!
0
 
jpaulinoCommented:
Can you show how are you formating ? You need to indicate as format "yyyy-MM-dd" and not "YYYY-MM-DD" or "yyyy-mm-dd" or other ... it's case sensitive. Also you must use "#" between the date like #2009-02-14#
I advice you to use parameters and you don't need to worry about that.
Check and example: http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23755388.html
 
0
 
sunithnairCommented:
Try changing your default language to British language in SQL server that will default the date formar to DD/MM/YY http://support.microsoft.com/kb/173907
0
 
chenyuhao88Author Commented:
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.Text.Tostring, "yyyy/mm/dd",nothing) again to create a Datetime object first then use in my SQL query. (I think the date setting on my vista convert user input "2009/01/15" to "15/01/2009", but SQL Server treats it as mm/dd/yyyy).

 

0
 
momi_sabagCommented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
 
jpaulinoCommented:
Can you show the SQL statement that you to insert the values and how do you format the dates ?
0
 
jpaulinoCommented:
If you use a datetimepicker you can do:
"INSERT INTO myTable (a,b,c) VALUES (1,2, '#" & Me.DateTimePicker1.Value.ToString("yyyy-MM-dd") & "#')"
More or less like this
0
 
chenyuhao88Author Commented:
jpaulino,

There is nothing special. Like what I posted, the SQL query =

"Select * from OrderHeader where  OrderType = 's'  and OrderDate >= ' " & Date.ParseExact(textDate.text.trim, "yyyy/mm/dd",nothing)  &  "  '
 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?


0
 
jpaulinoCommented:
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.ToString("yyyy/MM/dd") and you can try also with the "#"

"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"

Open in new window

0
 
chenyuhao88Author Commented:
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("yyyy/MM/dd")? Will SQL regard them as the same?

0
 
jpaulinoCommented:
If you do this:
Dim textDate As String = "2009/02/13"
Debug.WriteLine(Date.ParseExact(textDate, "yyyy/mm/dd", Nothing))
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.ToString("yyyy/MM/dd"))

End If
0
 
jpaulinoCommented:
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.
0
 
Éric MoreauSenior .Net ConsultantCommented:
whatever the date format on your system, you should always use the yyyy/MM/dd format when sending dates to SQL server:

Select * from OrderHeader where  OrderType = 's'  
and OrderDate >= '2006/01/15 12:01:00 AM'
 Order by CustomerBranchID, OrderDate

jpaulino, you wrote:

>>textDate.text.trim.ToString("yyyy/MM/dd") and you can try also with the "#"

A string (textDate.text.trim) cannot be converted to the ToString format yyyy/MM/dd, only dates can. Also the # can only be used with Access and not with SQL server
0
 
jpaulinoCommented:
>> only be used with Access and not with SQL server
I don't know if it access or SQL ...  :)<!-- richText -->
0
 
Éric MoreauSenior .Net ConsultantCommented:
>>I don't know if it access or SQL ...  :)

see the last phrase of the question.
0
 
jpaulinoCommented:
OMG ... I miss that  :)
I even showed some examples of using parameters in Access and not SQL!!!
0
 
chenyuhao88Author Commented:
Thanks all. It works fine now. I just use the universal format yyyy/mm/dd.

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 6
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now