Hi all,

Why is the date in SQL Server 2005 printed in this format..
Feb 9 2006 12:00AM

In the SQL Server DB it is stored in this format.. 09/02/2006

Why is the time added also? I don't want the time.

Thanks. And thanks for all your help so far. I'm finally getting the hang of SQL Server.

Kevin.

    That will be your default date format there, according to the language setting sprcified for your server. Internal storage of dates is as a number of days from base date of 1 January 1900, and presentation is governed by server settings at install (default language) or runtime. You can change it at runtime with SET DATEFORMAT, or use any of the date functions to manipulate and present the date as you prefer. Refer to SQL 2005 books online for more information.

      OK. So this is the standard mssql format.. Feb 9 2006 12:00AM ??

      In Enterprise Manager when I query its displayed as 09/02/2006

      I'm tring the CONVERT function but need a little help. Here's what I have...

      $query = "SELECT CONVERT(CHAR(8),class_date), start_datetime, end_datetime ".
                   "FROM class ".
                   "WHERE course_ID = '$course' ".
                   "AND location_ID = '$location' ".
                   "ORDER BY class_date DESC";

      Can you explain CONVERT syntax please.

      Thanks again.

      Kevin.

        Look, the only thing that is certain from the SQL2005 books online is that it stores the date data as a 4-byte number. So anything that shows you the date in any recognisable date format is formatting the integer into a date. What enterprise manager does and what queries return are governed by the date formatting in place.

        The error in your code is that you forgot the style-indicator (I use 106) in the CONVERT()

         $query = "SELECT CONVERT(CHAR(8),class_date, 106), start_datetime, end_datetime ".
                     "FROM class ".
                     "WHERE course_ID = '$course' ".
                     "AND location_ID = '$location' ".
                     "ORDER BY class_date DESC"; 
        

        That will return 09/02/2006.

        Typical of mickeysoft, most of the defined date formats are not what I want for display anyway, so doing it in the presentation code is where we are at again.

        The default display format in transact sql is aparently Mon dd, yyyy hh:mmAM/PM and when no time is present it defaults to 12:00AM.
        Fat lot of use the 12:00AM is if you have not stored a time then why would you want a default time?????

        Most of the others are 2 digit years ???? After Y2K, why???

        I just wish everyone would do the sensible thing and use the ISO 8601 datetime format yyyy-mm-dd hh:mm:ss Self-sorting, language independent, and available range from the year dot to 9999-12-31. I'm sure we'll have other requirements by the year 10,000 AD.

          Here's what I've used..

          $query = "SELECT CONVERT(char(8),class_date,106), start_datetime, end_datetime ".
                            "FROM class ".
                           "WHERE course_ID = '$course' ".
                           "AND location_ID = '$location' ".
                           "ORDER BY class_date DESC";

          class_date is stored as datetime type. However no value for $dt is printed. Why is this??
          $dt = $row['class_date'];
          echo '<p>class date:::'.$dt.'</p>';

            Debugging 102

            If you want to find out what your query is returning when the results are not what you expect

            // get to
            $row = mysql_fetch_array($result);
            // then
            print_r($row);
            
            

            See of you can spot what you have forgotten.

              hi Roger,

              It gives this...

              Array ( [0] => 09 Feb 2 [computed] => 09 Feb 2)

              I need to convert into this format...09/02/2006

              Or even remove the time from it.. Feb 9 2006 12:00AM

              Thanks.

                A couple of tips. As ramjet said, internally the server stores the timestamp as a couple of integers. So the date you see from your tools is converted in some manner which may be possible to configure (check PHP.INI mssql section).

                You should always alias any computed value in a SQL query so you can refer to it by name later:

                 $query = "SELECT CONVERT(CHAR(8),class_date, 126) AS iso_datetime, start_datetime, end_datetime ".
                             "FROM class ".
                             "WHERE course_ID = '$course' ".
                             "AND location_ID = '$location' ".
                             "ORDER BY class_date DESC"; 

                Notice I changed the "Convert" format code to 126 which is ISO 8601 format.

                When you get this value you can simply split the time part off:

                $row = mysql_fetch_array($result); 
                $iso_parts=explode(" ",$row['iso_datetime']);
                $whatIwant=$iso_parts[0];
                $formatIwant=date('m/d/Y',strtotime($whatIwant));
                //or more simply
                $formatIwant=date('m/d/Y',strtotime($row['iso_datetime']);
                

                The last line is the simplest to use as strtotime() "knows" how to handle ISO8601 timestamps (converting it to a unix timestamp) and the date() allows you to set your output format.

                  Hi guys,

                  This date issue is causing me a lot of headaches.
                  Yesterday this query worked perfectly...

                  INSERT INTO class(course_ID, location_ID, instructor_ID, class_start, class_end, class_date) VALUES('106', '3', '19', '14/02/2006 09:00:00', '14/02/2006 10:00:00', '14/02/2006')

                  Now it doesn't work.

                  Tested in Enterprise Manager and get this error...
                  The conversion of char data type to smalldatetime data type resulted in an out of range smalldatetime value

                  Why is this?
                  class_start, class_end are set as datetime types
                  class_date is set as smalldatetime type.

                    Is it expecting US format mm/dd/yyyy cos month 14 is 'out of range'. Depends on your server's locale, laguage, etc settings

                      Checked Regional and Language Options in the control panel.
                      Standards and Formats are set to English(Ireland).
                      Short Date is 14/02/2006
                      Long Date is 14 February 2006
                      Location is set to United States

                      So shouldn't the format I use(DD/MM/YYYY) work?

                      Thanks.

                        Use SET DATEFORMAT when you open your connection each time.

                        A well designed application should work correctly regardless of locale settings on the database server etc - getting screwed dates is not merely slightly inconvenient, it will cause serious data corruption. Your users will be pretty pissed off when they find that they have to re-enter loads of data because the day/month got swapped.

                        Mark

                          Just get with the program, use ISO 8601 dates for everything in every database and programming language - and if they don't support it then don't use them. Even people can read and understand the format yyyy-mm-dd, no matter what country they live in.

                          Now, if you want to make pretty output then that happens in the PRESENTATION layer/code. That is why edwardsbc told you to do it in php - native support for ISO 8601.

                          Get into the habit for every language and app cos that is where it is going: has to go.

                          Forget any date or time formats that are native to your country - they are just an interesting cultural artifact.

                          And definitely ignore any 'usefull' date functions that your db or language has that make 'native' date/time formats 'easier' to use: and that includes UNIX timestamps for all you *NIX-heads.

                          ISO8601 format date are self-sorting (year then month then day), easy to compare (year, month, day) and universally human-readable. All other date formats fail all of these functional tests.
                          Using month names fails on language problems - u may know the French for May (Mai) but do you know the Russian for August (&#1072;&#1074;&#1075;&#1091;&#1089;&#1090😉?

                            2 months later

                            thanks. this works..

                            			/**********************************
                            			FORMAT DATES
                            			**********************************/
                            			$newdate = date('d/m/Y', strtotime($row['class_date']));
                            			//echo '<p>'.$newdate.'</p>';
                            
                            		/**********************************
                            		FORMAT DB TIMES
                            		**********************************/
                            		$new_class_start = date('H:i:s', strtotime($class_start));
                            		//echo 'NEWTIME: '.$new_class_start.'<br />';
                            		$new_class_end = date('H:i:s', strtotime($class_end));
                            		//echo 'NEWTIME: '.$new_class_end.'<br />';
                            
                            		$class_time = $new_class_start.'-'.$new_class_end;
                            
                              Write a Reply...