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']); Code: if ($foliodata->Made_On < $threshold) 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: |
Welcome Guest, Not a member yet? Register Sign In |