accessing a MSSQL datetime using Active Record returns a string

[eluser]winter[/eluser]
Basically the title says it all. I've looked but haven't found much info on how Active Record processes MSSQL datetime date types. I'd like to do a comparison (if) with a datetime held in the MSSQL DB. Lot's of people on the net give examples building queries using CONVERT and the like.

I just wonder if the Active Record class has a nice clean way of dealing with date types. Or do I actually have to build my query and not use Active Record this time?

If so, that's fine. I just don't want to do hand stands if the solution is fairly simple.

Thanks guys for your help! d

[eluser]TheFuzzy0ne[/eluser]
Sorry, but I don't understand what you are trying to do. If you can show us the code you think you need to use, someone will hopefully confirm it.

[eluser]whobutsb[/eluser]
@winter I' not sure if this is what you are talking about. I found that when you are using Active Record and MSSQL and inserting datetime stamps I like to use:

$insert_data = array(
'datetime' => unix_to_human(now())
);

$this->db->insert('myTable', $insert_data);

unix_to_human(now()) seems to work fine inserting date stamps in to the database. And for search queries I will use something like this:
$search = date('Y-m-d', strtotime($yourDate));

I'm not sure if that answered your question or not.

But also I have a question when it comes to the MONEY format. If i'm inserting values in to a column with the MONEY format like so:
$array = array(
'value' => '42.23',
'tax' => '.32'
);

$this->db->insert('myTable', $array);

The query comes back with an error and looks like this:
INSERT INTO myTable ('value', 'tax') VALUES ('42.23', '.32');

The problem with this is that MSSQL thinks the values are varchar and you can't insert VARCHARS into a MONEY format. So when ever I enter in Money formats I need to write it as a standard query without the apostrophes around the values. Anyone know how to get around this and use standard Active Record?

[eluser]winter[/eluser]
[quote author="TheFuzzy0ne" date="1235498093"]Sorry, but I don't understand what you are trying to do. If you can show us the code you think you need to use, someone will hopefully confirm it.[/quote]

It's pretty simple:

Code:

$this->db->where('FolioID', $formdata['folioid']);
// return just the first row() as there should only be one
return $this->db->get('folio')->row();

Code:

if ($foliodata->Made_On < $threshold)
{
// do some stuff here
}

I really don't think there is a problem with the code.

$foliodata->Made_On is the timestamp of the row in the MSSQL DB. However, Active Record returns it as a string when it's actually stored as a datetime.

@whobutsb:

Thanks, for the advice. It helps a bit. What I need to do is compare the datetime to a var. I don't need to do a query with a datetime where. Also, yeah, if I was inserting the data as well, I'd use an int with a unixtime stamp. However, the data is already there. I'm only reading it. Though, I think you've confirmed it. Active Record might not handle MSSQL types correctly. It's too bad I have to use MSSQL for this project, but the DB already exists. So I guess I should build a query string.

Can any of the devs confirm this lack in Active Record?

Thanks!

[eluser]Relexx[/eluser]
php does not support a date data type (php data types) even php's date function returns a string

[eluser]winter[/eluser]
[quote author="Relexx" date="1235559632"]php does not support a date data type (php data types) even php's date function returns a string[/quote]

Yeah. I was sort of hoping for the unixtimestamp - integer... It would be nice if Active Record class handled it that way seeing as it is kind more robust than some string.

Anyway, what I ended up doing was:

Example of date returned from MSSQL DB using Active Record class:

Code:

Feb 1 2009 01:35:58:000PM

Parsing:

Code:

$madeon = date_create(date("m/d/Y",strtotime(substr($foliodata->Made_On, 0, strlen($foliodata->Made_On) -15))));

Maybe there is a shorter way of doing it. I'll try to whittle it down. I also loose the time, which would be nice to keep. For now that works. If any PHP gurus can see a way that is cheaper/more acurate, let me know.

Thanks!

[eluser]Relexx[/eluser]
is your mysql data type date or timestamp? I believe timestamp returns a different/numeric value.

[eluser]winter[/eluser]
[quote author="Relexx" date="1235566816"]is your mysql data type date or timestamp? I believe timestamp returns a different/numeric value.[/quote]

Thanks. That's nice. I don't know how many times I've said MSSQL. Actually, I just counted - 7. mSsql not mYsql. There is a difference.

[eluser]Relexx[/eluser]
[quote author="winter" date="1235571160"][quote author="Relexx" date="1235566816"]is your mysql data type date or timestamp? I believe timestamp returns a different/numeric value.[/quote]

Thanks. That's nice. I don't know how many times I've said MSSQL. Actually, I just counted - 7. mSsql not mYsql. There is a difference. [/quote]

Sorry an easy mistake to make :red:. I always refer MSsql as Sql Server, but that is more industry habit for myself.

[eluser]winter[/eluser]
[quote author="Relexx" date="1235584341"][quote author="winter" date="1235571160"][quote author="Relexx" date="1235566816"]is your mysql data type date or timestamp? I believe timestamp returns a different/numeric value.[/quote]

Thanks. That's nice. I don't know how many times I've said MSSQL. Actually, I just counted - 7. mSsql not mYsql. There is a difference. [/quote]

Sorry an easy mistake to make :red:. I always refer MSsql as Sql Server, but that is more industry habit for myself.[/quote]

You're right. MS calls their SQL database server "SQL Server". I don't know why they don't call Windows "OS". :roll: