If I understand your post correctly, you are currently using tables with one field of type int, and want to store different types of data in it.

The proper way in SQL databases to separate different data types is by either storing them in different fields in one table, or in different tables. This depends on the type of information you want to store in the database.

Without knowing your application it is difficult to help you in the right direction. My advice to you is to use the Microsoft SQL Server 2005 books and tutorials at [msdn2.microsoft.com...] and [msdn2.microsoft.com...] and see if the examples presented there match the type of data you want to store. These documents are a good starting point for you to get a better insight in what databases are, and how they can be structured.

Why not just use the DATETIME (or SMALLDATETIME) type instead of creating a whole new data type?

If I understand your post correctly, you are currently using tables with one field of type int, and want to store different types of data in it.

No, I am using tables with many fields that have the datatype "int", and I want to stores ints on them, the problem is that one of those fields that int will be parsed after being retrieved from the table (By php) so I was wondering if there is a way to distinguish that int field from others but still keeping ints stored in them

As for DATETIME I was originally using that but the problem is that I did not manage after many tried to make the retrivable data to show a different date format other than the American one (mm/dd/yy) and being able to use php's date function is a lot easier and more comfortable on the programming side since the whole application is written in php.

To make it short, PHP stores dates as int(10), so I can easily store dates in the database on a field with the int datatype but when retrieving many fields at once I can't find a way to tell if the current int field being retrieved is a date or just a normal int because I can't differentiate the fields since they are all of type int.

As for DATETIME I was originally using that but the problem is that I did not manage after many tried to make the retrivable data to show a different date format other than the American one (mm/dd/yy)

So you are compromizing the data storage and using INT instead of DATETIME, because retrieving the dates and converting it seems easier in PHP.

I would recommend storing it in DATETIME as this will make portability of the data and usage of external query tools much easier. Instead of the PHP conversion functions, you can use the DATE_FORMAT() function which formats the date in every format you want. This has the same (or even more) flexibility than manipulating the date in PHP. The only difference is that it is not a PHP command, but is embedded in the SELECT query. For example:

SELECT DATE_FORMAT(`date`,'%m/%d/%y') AS `american`,DATE_FORMAT(`date`,'%d-%m-%Y') AS `other` FROM `yourtable` WHERE some_condition;

This will retrieve column `date` twice from your table, first as mm/dd/yy and as dd-mm-yyyy.

STR_TO_DATE is the opposite from DATA_FORMAT and can be used when inserting or updating date fields.

I agree w/ Lammert and others here. If you are storing a date in SQL*Server, you should be storing it using one of SQL*Server's date data types - DATETIME or SMALLDATETIME (depending on whether you need seconds and milliseconds or not).

You don't have to do the date reformatting in PhP. You can reformat the date using SQL*Server date functions.

On my SQL*Server database the default DATETIME format displays dates as '2000-12-21 17:19:34.737' if I simply issue a SELECT statement like:

SELECT InsertDateTime FROM tReferral

If I wanted say to only display the date portion and do it in MM/DD/YYYY format I might perform the following query:

SELECT
CONVERT(VARCHAR,DATEPART(Month, InsertDateTime))+'/'+
CONVERT(VARCHAR,DATEPART(Day, InsertDateTime))+'/'+
CONVERT(VARCHAR,DATEPART(YYYY, InsertDateTime))
FROM tReferral

If InsertDateTime is a DATETIME or SMALLDATETIME data attribute in a table named tReferral. This would output the date portion of the field as '12/21/2000'

If you store a PhP type/Un*x type date time INT value (number of seconds since Jan 1, 1970 00:00) in the DB, then you can't use SQL*Server date functions to compare these. You can't even select the dates out of your DB using the query window in SQL Server Management Studio an know what date they represent. You'll just get some huge number. Storing them as INT in the DB will prevent you from doing date comparisons in Stored Procedures, etc. Just not a good idea in general IMO.

I would store dates in the DB as DATETIME. In PhP you can select them out into a format that you can pass to PhP's strtodate function to convert it to a Un*x style date time if you need to do comparisons to other Un*x date times.

[edited by: ZydoSEO at 6:28 pm (utc) on Jan. 14, 2008]

arrrrrrg in last paragraph above I meant 'strtotime', not 'strtodate'. :)

Thanks, I had tried that without any luck (The date kept coming in the same format rather than the one I told it to be) but I guess I'll try again.

Is there anyway I can retrieve all fields of a table (Like SELECT * FROM Table) but tell it that the date field shoudl be in a certain format?

Here's what I had trouble with when trying DATETIME the first time.

I tried inserting a the date in a certain format in the field so that when I retrieved the whole table the date would come back in that format but that didn't work well because the date kept coming back in the default formatting even though I had inserted it in a different format.

Retrieving the date in a certain format seems to be a problem because I'd have to single out the DATETIME field in a query and another one for the all the other fields (And writing every field in a select statement is not an option as it's too many fields and the table retrieved are not always the same as the code can retrieve different tables dinamically).

Can anybody see any solution to this problem? I thought using PHPs date format instead would solve all of the above problems and indeed it does if I can just distinguish the date field from a normal int field.

What I am looking for is something of the sort:

SELECT * FROM table formatting all datetime fields to a certain format WHERE.......

Is this possible?

[edited by: NooK at 9:47 am (utc) on Feb. 11, 2008]

The database itself determines how it stores the data. So formatting it differently before inserting it won't do anything, because it will automatically reformat your input to the way it stores it.

You can do a SELECT * to pull all the data you need, then format it appropriately when you display it.

You COULD store the data as a VARCHAR field instead of DATETIME, but then you lose the built-in functionality of the DATETIME type.

Alternatively, if the data doesn't change much, you COULD also write the data both ways- have one field DATETIME data and another VARCHAR data. But you'll need to make sure that every single UPDATE line of your code updated both fields, OR inglude a TRIGGER to handle updates in the background for you.

But in the end, it makse much more sense to just change the way the data is displayed in PHP after you retrieve the data.

Retrieving the date in a certain format seems to be a problem because I'd have to single out the DATETIME field in a query and another one for the all the other fields (And writing every field in a select statement is not an option as it's too many fields and the table retrieved are not always the same as the code can retrieve different tables dinamically).

Can anybody see any solution to this problem?

I'm a bit baffled as to why you have this "problem" in the first place, as there is no way in SQL to apply some formatting to every field in a query! You must be doing this conversion, in a loop, in PHP?

The solution to your problem is simple, you can name fields and use *. In this case, * refers to the remaining fields.

SELECT DATE_FORMAT(`date`,'%d-%m-%Y'), * from my_table;

With some databases, you may not be able to do this, and will have to add date redundantly to the query as a different variable:

SELECT DATE_FORMAT(`date`,'%d-%m-%Y') as euro_date, * from my_table;

I see where you're going with this, but the problem is

I can run SELECT CONVERT(CHAR(19), DateColumn, 120),* FROM someTable

But it happens what I had expected, rather than getting the column I want in the right formatting, I get an added column with the right formatting, but the original column is still in the result.

I'll probably have to work with that but isn't there anyway to avoid getting a new column and have the original one returned in the right format?

As for formatting the data on PHP side, it would mean doing some string manipulation to rewrite the date which feels wrong and unnecessary since php has a date formatting function (For php date format) and so does SQL.

As for formatting the data on PHP side, it would mean doing some string manipulation to rewrite the date which feels wrong and unnecessary since php has a date formatting function (For php date format) and so does SQL.

Exactly- SQL has date formatting functionality, which is exactly what jtara suggested.

but isn't there anyway to avoid getting a new column and have the original one returned in the right format?

Yes, you specify the columns you want instead of using the * wildcard.

You need to understand that SQL (and other DBs) do not store dates as a string like "February 12, 2008 8:47.04 AM" because character data takes up much more space than binary data. SQL takes your input, converts it into the date/time format that it uses for internal storage, then spits back that same data in its default format when retrieved. It has no way of tracking or knowing how the data was originally formatted when input. Thus the various date/time formatting functions.

Think of it like if you deposited $100 in a bank. When you take the money out, the bank teller (or ATM) doesn't know (or care) whether you deposited 1 $100 bill, 5 $20 bills, 10 $10 bills, or $100 in coins. He/she/it gives you your money in the default way (usually 5 $20 bills), unless you specify otherwise each time you take the money out.

If you insist on getting the exact format without any other types of manipulation, then see my previous post about using a VARCHAR field.

I get an added column with the right formatting, but the original column is still in the result

What's wrong with having the extra column?

Just don't use the original.

Example:

select name as extra_name, * from address;

OK, so you've got name and extra_name.

The blue won't fall out of the sky because you've also got two names in the result! You don't have to use it!

What's wrong with having the extra column?

In general, nothing, but if one needs to iterate through the columns with a loop it may not be a good idea to have a "clonse" column

I'll just have to specify the columns in the select statement but there should really be a way of being able to format a column using a wildcard without getting an extra column and/or exclude columns from a query when using wildcard.

Thanks for the input guys

if one needs to iterate through the columns with a loop it may not be a good idea to have a "clonse" column

If you need to loop through columns instead of using specific column names, methinks that you are creating much more of a mess down the road than you realize, in terms of the DB structure and/or your process. Since you mentioned that you are new to DBs in general, chances are that you are trying to do things in ways that DBs are not designed for and could be creating a lot of inefficiency in the DB as well as extra work for you.

Perhaps you could list a short overview of what you are trying to accomplish with a small set of sample data?

You can change the default date behavior in SQL Server by adding a language. To do this you use the sp_addlanguage stored procedure. The following example changes dates to French format.

sp_addlanguage french, null,
"janvier,fevrier,mars,avril,mai,juin,juillet,
aout,septembre,octobre,novembre,decembre",
"jan,fev,mars,avr,mai,juin,jui,aout,sept,oct,
nov,dec",
"lundi,mardi,mercredi,jeudi,vendredi,samedi,
dimanche",
dmy, 1

Yes SQL suppoorts user defined types. Create the new data type, the n the rules, then bind the rules to the type:

EXEC sp_addtype N'myDate', N'datetime', N'not null'
GO
create rule [myDateRule] as Len(@myDate)=8
GO
EXEC sp_bindrule N'[dbo].[myDateRule]', N'[myDate]'
GO

If you are querying the schema and looping through columns you could just use extended properties rather than a new data type.

As a general rule personally I would always store and retrieve all dates as SmallDateTime and work the format in the application not the database, otherwise you apps will break when upgrading databases versions.

If this is no good to you, then you can use the SQL Format or Convert functions to change the layout when selecting.

If its still not what you want then you can create a SQL UDF like above to apply your own special format.

Thanks.

I have some tables with order information holding data such as order date type, order specifics (Depending on the type of order) and such.

The reason I wanted to iterate through the table is, because It would save having to change much in the html code in the future in case more columsn are added/removed from the database tables. Also another main reason is that the order specific tables will have the data retrieved from them dinamically, meaning that the html table will display many different orders and depending on the type of order different tables might be chosen to have it's data retrieved and such, and because of that (One can't know exactly which table will be called forth for each user and order) it is easier to iterate through them.

The reason I wanted to try and use php data is because the different stages of an order will be logged with a date set on each and those dates will be used in calculations later on for statistics and I think working those dates in php would probably be better than working them out on the SQl side itself.

It's still kind of difficult to understand what you're trying to do without specific sample data. But even assuming that you "must" loop over every field, you can still easily do this.

Make the name of the actual database field "RealDateTime" and store the datetime data there. When you pull it out, alias it as a different name (like "SELECT CONVERT(CHAR(19), DateColumn, 120) AS FormattedDate,* FROM someTable"). WHen you loop through the data to display, ignore the field called "RealDateTime" (I don't know PHP so can't tell you how to do this specifically).