SQL Server Date Issue

[obrienkev]

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.

[afrika]

where are you printing it out ?

webpage, QA ???

[r937]

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

actually, it isn't

it is stored as two integers, one for the date (number of days past a "zero" date, which is january 1, 1900, i think) and one for the time (number of seconds past midnight)

to display dates in other than the default format, use the CONVERT function

[obrienkev]

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.

[briansol]

CAST and CONVERT
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax
Using CAST:

CAST ( expression AS data_type )

Using CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments
expression

Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions.

data_type

Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types.

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.

style

Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports the date format in Arabic style, using Kuwaiti algorithm.

In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

type convert into query analyzer, highlight, shift+f1. you get the help screen with tons of info on how to use it.


if you want just the date, i recommend you create a user function.

this is what i use:

EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
USE MASTER
GO
create function system_function_schema.fn_dateonly(@date datetime)
returns varchar(12)
with schemabinding
as 
	begin
	return convert(varchar(12), @date, 101)
	end
	go
EXEC SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE

EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
USE MASTER
go
grant execute on system_function_schema.fn_dateonly to USERNAME
EXEC SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE

call it via:

select fn_dateonly(getdate())

this will return the date only, no time.

[r937]

briansol, you have a 20-line function that returns the same as a CONVERT? and what if you wanted to see it in some other format besides style 101?

whoa

kevin, i'm not gonna explain CONVERT, the documentation (Books Online) does a much better job, i suggest you look there first

[briansol]

the function itself is nothing more than a convert. the rest of it is for creating the function.

When doing queries to check for the date only, i see no need for any other style than 101.

for example, get toady's records:

SELECT * FROM bar where fn_dateonly(datetimefield) = fn_dateonly(getdate())

nice and clean and easy to use

[r937]

SELECT * FROM bar where fn_dateonly(datetimefield) = fn_dateonly(getdate())

nice and clean and easy to use

agreed, nice and clean and easy to use, however, it will not use an index on the date column and therefore won't scale

this is much better for performance --

where datetimefield >= '2006-02-09' and datetimefield < '2006-02-10'

[obrienkev]

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>';

[r937]

you're asking for class_date but your result set doesn't have a column by that name

you need to give the expression a column alias --

SELECT CONVERT(char(8),class_date,106) as foo
$dt = $row['foo'];

[obrienkev]

Tried..
$row = mssql_fetch_array($result);
echo '<p>';print_r($row);echo '</p>';

It gives this...

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

Using the column alias didnt make a difference.

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

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

Thanks.

Kevin.

[r937]

then why did you choose style 106? that's dd mon yyyy

[obrienkev]

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.

[r937]

try it like this --

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

notice the differences? (there are 6 of them)

how come you never answered me in that other thread yesterday about combining your three datetime fields into two?

[obrienkev]

Hi Rudy,

Haven't had the chance to check other post yet. I use 3 date columns cos it makes it easier to compare with date user enters. - save me messing about with a lot of queries again.

Should date/time always be entered in this format..YYYY-MM-DD

Thanks.

[r937]

Haven't had the chance to check other post yet.

best get cracking, then

I use 3 date columns cos it makes it easier to compare with date user enters.

actually, no it doesn't, but i think you will not realize this until you actually write some SQL

Should date/time always be entered in this format..YYYY-MM-DD

not necessarily, but it is a format that always works