473,696 Members | 1,742 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

datetime problem

Is correct to have a varchar field and insert dates of type dd/mm/yyyy
into it? I choose this method because I have an application that runs
on more than one server. So, if I used a datetime field (MSSQL Server)
it worked on my test machine. If I run the same application on another
machine with different regional settings is not working. I tried with
date_default_ti mezone_set to change timezone but is not changing. After
date_default_ti mezone_get the result was the new timezone but the dates
were the same. So, after that I changed all datetime fields into
varchar and used query's like this (see bellow) where I have date
operations.

My question: is correct to to this? Because is working on one machine
and on another one I get an error that it can't convert char to
datetime. My guess is that I left a datetime field unchanged or my
ideea about using varchar instead of datetime is completely wrong.

SELECT * FROM DocD WHERE CONVERT(datetim e,created,101) BETWEEN
CONVERT(datetim e,'10/25/2006',101) AND
CONVERT(datetim e,'10/25/2006',101) ORDER BY cName ASC,
CONVERT(datetim e,created,101) DESC, DocID DESC

Oct 25 '06 #1
5 2597

iulian.ilea wrote:
Is correct to have a varchar field and insert dates of type dd/mm/yyyy
into it? I choose this method because I have an application that runs
on more than one server. So, if I used a datetime field (MSSQL Server)
it worked on my test machine. If I run the same application on another
machine with different regional settings is not working. I tried with
date_default_ti mezone_set to change timezone but is not changing. After
date_default_ti mezone_get the result was the new timezone but the dates
were the same. So, after that I changed all datetime fields into
varchar and used query's like this (see bellow) where I have date
operations.

My question: is correct to to this? Because is working on one machine
and on another one I get an error that it can't convert char to
datetime. My guess is that I left a datetime field unchanged or my
ideea about using varchar instead of datetime is completely wrong.

SELECT * FROM DocD WHERE CONVERT(datetim e,created,101) BETWEEN
CONVERT(datetim e,'10/25/2006',101) AND
CONVERT(datetim e,'10/25/2006',101) ORDER BY cName ASC,
CONVERT(datetim e,created,101) DESC, DocID DESC
Well, first of all, if you are storing a string that is always exactly
the same length (in this case 10 characters) then it should be in a
fixed length CHAR field rather than a VARCHAR one.

Next, you make a statement "If I run the same application on another
machine with different regional settings is not working.".

This is probably the most important piece of information you could have
supplied in this question, but all you have said is that it doesn't
work! Does the whole server break? Does the MySQL instance crash? Does
any code (php, asp, perl, ...) used to access the database crash? Does
the date get produced but in the wrong format? Does the SQL simply
return an SQL error? ...

I could go on! You need to tell us WHAT the problem is!

Then we might be able to help you solve it.

Oct 25 '06 #2
iulian.ilea wrote:
Is correct to have a varchar field and insert dates of type dd/mm/yyyy
into it? I choose this method because I have an application that runs
on more than one server. So, if I used a datetime field (MSSQL Server)
it worked on my test machine. If I run the same application on another
machine with different regional settings is not working. I tried with
date_default_ti mezone_set to change timezone but is not changing. After
date_default_ti mezone_get the result was the new timezone but the dates
were the same. So, after that I changed all datetime fields into
varchar and used query's like this (see bellow) where I have date
operations.

My question: is correct to to this? Because is working on one machine
and on another one I get an error that it can't convert char to
datetime. My guess is that I left a datetime field unchanged or my
ideea about using varchar instead of datetime is completely wrong.

SELECT * FROM DocD WHERE CONVERT(datetim e,created,101) BETWEEN
CONVERT(datetim e,'10/25/2006',101) AND
CONVERT(datetim e,'10/25/2006',101) ORDER BY cName ASC,
CONVERT(datetim e,created,101) DESC, DocID DESC
I think it should work. But it's a lot of unnecessary complication and
work.

Fix your problem with the datetime column. You might try
alt.databases.m ssql for some help on it.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===
Oct 25 '06 #3

Jerry Stuckle wrote:
iulian.ilea wrote:
Is correct to have a varchar field and insert dates of type dd/mm/yyyy
into it? I choose this method because I have an application that runs
on more than one server. So, if I used a datetime field (MSSQL Server)
it worked on my test machine. If I run the same application on another
machine with different regional settings is not working. I tried with
date_default_ti mezone_set to change timezone but is not changing. After
date_default_ti mezone_get the result was the new timezone but the dates
were the same. So, after that I changed all datetime fields into
varchar and used query's like this (see bellow) where I have date
operations.

My question: is correct to to this? Because is working on one machine
and on another one I get an error that it can't convert char to
datetime. My guess is that I left a datetime field unchanged or my
ideea about using varchar instead of datetime is completely wrong.

SELECT * FROM DocD WHERE CONVERT(datetim e,created,101) BETWEEN
CONVERT(datetim e,'10/25/2006',101) AND
CONVERT(datetim e,'10/25/2006',101) ORDER BY cName ASC,
CONVERT(datetim e,created,101) DESC, DocID DESC

I think it should work. But it's a lot of unnecessary complication and
work.

Fix your problem with the datetime column. You might try
alt.databases.m ssql for some help on it.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===
Captain Paralytic, this is a PHP group so, obviously that is ask
questions PHP related. I use MSSQL not MySQL. Yes, the entire server
machine breaks because of an simple SQL select statement. Are you
kidding, right? The problem was on the select, that is why I asked if
is correct to do this.

Jerry, it works ok now that I discovered the problemm.

The problem:
I had Remote Desktop Connection this afternoon to the machine with
problems. I checked if all fields that were datetime in the past are
now varchar and all was ok. The problem was only on one table that
stores data (and I had 5 tables that are using the same code so I
thought to delete all record from that table). I delete all records and
it works now. Maybe the 'created' field from table stored data of type
datetime and that caused this problem on convert.

Oct 25 '06 #4
iulian.ilea wrote:
Jerry Stuckle wrote:
>iulian.ilea wrote:
>>Is correct to have a varchar field and insert dates of type dd/mm/yyyy
into it? I choose this method because I have an application that runs
on more than one server. So, if I used a datetime field (MSSQL Server)
it worked on my test machine. If I run the same application on another
machine with different regional settings is not working. I tried with
date_default_ timezone_set to change timezone but is not changing. After
date_default_ timezone_get the result was the new timezone but the dates
were the same. So, after that I changed all datetime fields into
varchar and used query's like this (see bellow) where I have date
operations.

My question: is correct to to this? Because is working on one machine
and on another one I get an error that it can't convert char to
datetime. My guess is that I left a datetime field unchanged or my
ideea about using varchar instead of datetime is completely wrong.

SELECT * FROM DocD WHERE CONVERT(datetim e,created,101) BETWEEN
CONVERT(datet ime,'10/25/2006',101) AND
CONVERT(datet ime,'10/25/2006',101) ORDER BY cName ASC,
CONVERT(datet ime,created,101 ) DESC, DocID DESC
I think it should work. But it's a lot of unnecessary complication and
work.

Fix your problem with the datetime column. You might try
alt.databases. mssql for some help on it.

--
============== ====
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
============== ====

Captain Paralytic, this is a PHP group so, obviously that is ask
questions PHP related. I use MSSQL not MySQL. Yes, the entire server
machine breaks because of an simple SQL select statement. Are you
kidding, right? The problem was on the select, that is why I asked if
is correct to do this.
Just have a look through the message headers and you will see how many
people post problems here that are actually about HTML, server
configuration or database and nothing to do with PHP.

You made no explicit reference to PHP, and you used the phrase that all
support engineers hate: 'is not working' (or the more common variant
'doesn't work').

Captain Paralytic made some suggestions, and invited you to clarify your
problem, and you responded with sarcasm.
Jerry, it works ok now that I discovered the problemm.

The problem:
I had Remote Desktop Connection this afternoon to the machine with
problems. I checked if all fields that were datetime in the past are
now varchar and all was ok. The problem was only on one table that
stores data (and I had 5 tables that are using the same code so I
thought to delete all record from that table). I delete all records and
it works now. Maybe the 'created' field from table stored data of type
datetime and that caused this problem on convert.
So it was a PHP problem them?

Colin
Oct 29 '06 #5

Colin Fine wrote:
iulian.ilea wrote:
Jerry Stuckle wrote:
iulian.ilea wrote:
Is correct to have a varchar field and insert dates of type dd/mm/yyyy
into it? I choose this method because I have an application that runs
on more than one server. So, if I used a datetime field (MSSQL Server)
it worked on my test machine. If I run the same application on another
machine with different regional settings is not working. I tried with
date_default_t imezone_set to change timezone but is not changing. After
date_default_t imezone_get the result was the new timezone but the dates
were the same. So, after that I changed all datetime fields into
varchar and used query's like this (see bellow) where I have date
operations.

My question: is correct to to this? Because is working on one machine
and on another one I get an error that it can't convert char to
datetime. My guess is that I left a datetime field unchanged or my
ideea about using varchar instead of datetime is completely wrong.

SELECT * FROM DocD WHERE CONVERT(datetim e,created,101) BETWEEN
CONVERT(dateti me,'10/25/2006',101) AND
CONVERT(dateti me,'10/25/2006',101) ORDER BY cName ASC,
CONVERT(dateti me,created,101) DESC, DocID DESC

I think it should work. But it's a lot of unnecessary complication and
work.

Fix your problem with the datetime column. You might try
alt.databases.m ssql for some help on it.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===
Captain Paralytic, this is a PHP group so, obviously that is ask
questions PHP related. I use MSSQL not MySQL. Yes, the entire server
machine breaks because of an simple SQL select statement. Are you
kidding, right? The problem was on the select, that is why I asked if
is correct to do this.
Just have a look through the message headers and you will see how many
people post problems here that are actually about HTML, server
configuration or database and nothing to do with PHP.

You made no explicit reference to PHP, and you used the phrase that all
support engineers hate: 'is not working' (or the more common variant
'doesn't work').

Captain Paralytic made some suggestions, and invited you to clarify your
problem, and you responded with sarcasm.
Jerry, it works ok now that I discovered the problemm.

The problem:
I had Remote Desktop Connection this afternoon to the machine with
problems. I checked if all fields that were datetime in the past are
now varchar and all was ok. The problem was only on one table that
stores data (and I had 5 tables that are using the same code so I
thought to delete all record from that table). I delete all records and
it works now. Maybe the 'created' field from table stored data of type
datetime and that caused this problem on convert.
So it was a PHP problem them?

Colin
I don't want to comment anything you wrote above because I think it has
no sense. But I will answer to last question: like I wrote in my first
post I tried to change default timezone and I couldn't. In fact I could
change it but I had the same problem. That is why I choose to have a
varchar field type instead of datetime and make those sql conversions.
I think it was more a sql problem. But it still remains the mystery :)
- why I got the same error even if I changed default timezone?

Oct 30 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2827
by: Mingus Tsai | last post by:
Hello- please help with unpickling problem: I am using Python version 2.3.4 with IDLE version 1.0.3 on a Windows XPhome system. My problem is with using cPickle to deserialize my pickled arrays of datetime.datetime instances. The following is the code I have written: import cPickle, datetime import Numeric
2
15891
by: Rey | last post by:
Howdy all. My problem deals w/inserting nulls into database (SQL Svr 2K) for the datetime fields activityDate and followUpDate where nulls are allowed. >From the web form, the user can type in/select the activity or followup date. If, for this exercise, the followupdate textfield is empty, then when the insert method of the class calls the InsertActivityLog stored procedure, how can I get nulls inserted instead of 1/1/1900 because of
8
1865
by: Alan Silver | last post by:
Hello, I have a custom control that displays the date and time. I currently have properties for the day, month, year, hour and minute, which have to be set separately. This is inefficient. I would like to have one property that I can use to pass in a DateTime, and have the control pull out the various bits from that. I tried ... public DateTime DateTimeDT {
5
1998
by: I am Sam | last post by:
I have created this DateTime object and instanced it I think correctly DateTime myClubNow1=new DateTime(DateTime.Now.Year,DateTime.Now.Month,DateTime.Now.Day,DateTime.Now.Hour,DateTime.Now.Minute,DateTime.Now.Second); I keep getting the below error: Object reference not set to an instance of an object. I don't know what the problem could be. Can someone help me with this? It
9
4920
by: Phil B | last post by:
I am having a problem with a datetime from a web services provider The provider is sending the following SOAP response <?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tns="urn:JadeWebServices/WebServiceProvider/" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
3
3132
by: pranesh.nayak | last post by:
I'm facing an error:"String was not recognized as a valid DateTime" whille accessing DateTime from webservice. And when I try to set DateTime to the same webservice it fails with error:"Date value is of wrong type" it looks like xs:DateTime is not compatible with .Net DateType. Any suggestions on how to fix this without touching the webservice (3rd party webservice)?
6
1856
by: JFieseler | last post by:
Hi all, in a huge project i have the following problem. I create an object which contains many private members (i know that this is not correct, but it is a single use migration program). The members will be filled from database tables. In the fill method i see in debug mode the correct values. At the source where i create the object the DateTime members of the object are not present (error: cannot obtain value). An statement like this...
4
7244
by: =?Utf-8?B?QmFidU1hbg==?= | last post by:
Hi, I have a GridView and a SqlDataSource controls on a page. The SqlDataSource object uses stored procedures to do the CRUD operations. The DataSource has three columns one of which - "Modified" of type DateTime - is hidden since it should not be edited by a user. The system handles the update for this column. So, I have hidden (Visible=false) this column on the grid. In order to access the value in this field, I have created a...
4
3694
by: =?Utf-8?B?c2lwcHl1Y29ubg==?= | last post by:
Hi I am having a problem formatting a string when the time is in format hh.mm.ss - used in Europe Parse seems ok when the date uses "/" or "." as seperator but I get an exception when time uses "." as seperator as used in Europe I have US regional setting and I have tried switching my regional setting to Europe
0
8658
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8594
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9142
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8873
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6512
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5853
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4352
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2310
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1989
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.