Solved

MSSQL date time format

Posted on 2006-10-25
18
23,021 Views
Last Modified: 2011-08-18
ive got a MSSQL date / time format like:

Oct 24 2006 12:00AM

im trying to change it so it displays like:

24/Oct/2006 using the mktime() but im getting:

Warning: mktime() expects parameter 4 to be long, string given in c:\Inetpub\wwwroot\prelease\index.php on line 549
01/Jan/1970

My code:

echo date('d/M/Y',mktime(0,0,0,$datein));
0
Comment
Question by:ellandrd
18 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 170 total points
ID: 17801789
>ive got a MSSQL date / time format like:
>Oct 24 2006 12:00AM
do you actually have a datetime field or a varchar field?

on a datetime field, use the convert in the query itself:

select convert(varchar(40), yourfield, 106) from yourtable
resp:
select replace(convert(varchar(40), yourfield, 106) , ' ' , '/') from yourtable


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
0
 
LVL 49

Accepted Solution

by:
Roonaan earned 170 total points
ID: 17801792
You could try working with strtotime, but I doubt it will accept that date format. strptime should when your locale is set on english

Try:

$array = strptime($date, '%b %e %Y %H:%M%r'); //(when using Oct 1 2006)
or
$array = strptime($date, '%b %d %Y %H:%M%r'); //(when using Oct 01 2006)

$stamp = mktime($array['tm_hour'], $array['tm_min'], $array['tm_sec'], $array['tm_mon'], $array['tm_mday'], $array['tm_year']);

echo date('d/M/Y/, $stamp);

-r-
0
 
LVL 18

Assisted Solution

by:ingwa
ingwa earned 160 total points
ID: 17801868
I would recommend against changing your timestamp field.  Keep it in your database and update the time by using update tablename set mydatefield = NOW() where myotherfield = y;

When formatting the string, the database has very powerful formatting capabilities.  The sql I would use for the above would look like:

select date_format(mydatefield, '%b %d %Y %h:%i%p') as formatteddate from tablename where myotherfield = 'foo'; and that would give you an output of Oct 24 2006 12:00AM.  There are many other formatting parameters that you can give the date_format function.

I find it is best to use the database whereever possible to do date and time formats, along with date and time calculations.  This then frees up php to actually process the other data.  
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17801876
>>do you actually have a datetime field or a varchar field?

datetime

>>select convert(varchar(40), yourfield, 106) from yourtable...

will convert(varchar(40), ...) will in a query string for PHP?

>>You could try working with strtotime

I will take a look at this...
0
 
LVL 18

Expert Comment

by:ingwa
ID: 17801881
%b = Abbreviated month name (Jan..Dec)
%d = Day of the month, numeric (00..31)
%Y = Year, numeric, 4 digits
%h = Hour (01..12)
%i = Minutes, numeric (00..59)
%p = AM or PM
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17801955
>>I would recommend against changing your timestamp field.  Keep it in your database and update the time by using update tablename set mydatefield = NOW() where myotherfield = y;

do you want my Senior Programmer to kill me.  Our database is used by a large number of people and other web applications so changing it will cause more problems that good so your answer is no.
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17801970
Roonaan,

Im getting:

Call to undefined function strptime()

Im running PHP 5.1.4
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17802031
AngelIII

when i try using convert() i get this:

Warning: mssql_result() [function.mssql-result]: DateIn field not found in result in c:\Inetpub\wwwroot\prelease\index.php on line 543

My query:

$query = "SELECT CONVERT(varchar(40),DateIn,106) FROM tblBillables, tblItem WHERE tblBillables.BillableID = tblItem.BillableID AND tblBillables.NameID = ".$_SESSION['user_info']['persid']." AND tbl.Item.BillableID = ".$_GET['bid']." ORDER BY tblItem.Day, tblItem.Month";
            
0
 
LVL 18

Expert Comment

by:ingwa
ID: 17802170
Ellandrd, by running the convert you are changing your datetime field to a varchar with a length of 40.  If your Senior Programmer is going to kill you, then I suggest you start running immediately because he's after you...Lol.

By running the date_format() code as provided, you will find that your formatting of the information will come out nicely.  I must clearly state though that by running the date_format function, you will not change any data in the database, you are merely changing the way it's outputted at runtime.  For example:

select date_format(mydatefield, '%b %d %Y %h:%i%p') as formatteddate from tablename where myotherfield = 'foo';

and select date_format(mydatefield, '%M %d %Y %H:%i:%s') as formatteddate from tablename where myotherfield = 'foo'

will return two seperate formats of the date, but the data in the data will remain unchanged.

I would never provide code that would change the integrity of your data...just the way it looks when you output it.

Hope this helps.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:ingwa
ID: 17802173
but the data in the data should read but the data in the database.
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17802179
ok i will give this a try...

0
 
LVL 16

Author Comment

by:ellandrd
ID: 17802240
Warning: mssql_query() [function.mssql-query]: message: 'date_format' is not a recognized function name. (severity 15) in c:\Inetpub\wwwroot\prelease\index.php on line 542
0
 
LVL 18

Expert Comment

by:ingwa
ID: 17802332
umm, sorry it should be uppercase.  select DATE_FORMAT(`mydatefield`, '%b %d %Y %h:%i%p') as formatteddate.

If that doesn't work, what version of mysql are you running?
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17802390
its not MYSQL its MSSQL
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17802564
Ok ive managed to kinda workaround it using explode().  its probably not the best or most efficient way but none of the solution/ functions provided seem to work.

my "workaround" is as follows:

$parts = explode(' ',$datein);
echo $parts[1].'/'.$parts[0].'/'.$parts[2];

Thanks to everyone who took some time to help me...

ellandrd

p.s the accepted comment was not given to any particular expert for any reason, i just selected the first reply as i have to select an accepted comment as we all know... points split.
0
 
LVL 18

Expert Comment

by:ingwa
ID: 17802727
ellandrd, sorry I wasn't able to provide specific information for mssql...hadn't correctly read the question...but thanks for the split...hopefully my example can help you in the future when you use mysql.
0
 
LVL 16

Author Comment

by:ellandrd
ID: 17802801
oh yes, im sure it will.  the functions and advice i got today will be referred back to when i have more issues.

i alway take on board any help i get from EE experts so next time i dont have to ask a question - instead start answering more of them for others! its a great learning curve  - hey? ;-)

ellandrd
0
 
LVL 18

Expert Comment

by:ingwa
ID: 17803282
Definately a good learning curve...I don't know what I would have done without EE to begin with.  Good luck with your project, and learn hard :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Is this error or Notice in php error log? 6 32
Php pie charts 3 26
php refresh button on the browser 2 36
Update from TABLE-A to TABLE-B 5 39
Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now