SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Server Date Issue

    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.

  2. #2
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    where are you printing it out ?

    webpage, QA ???

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by obrienkev
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...
    PHP Code:
     $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.

  5. #5
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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.
    Last edited by briansol; Feb 9, 2006 at 11:08.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by briansol
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's what I've used..
    PHP Code:
     $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>';

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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'];
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    then why did you choose style 106? that's dd mon yyyy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guys,

    This date issue is causing me a lot of headaches.
    Yesterday this query worked perfectly...
    PHP Code:
    INSERT INTO class(course_IDlocation_IDinstructor_IDclass_startclass_endclass_dateVALUES('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.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try it like this --
    Code:
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by obrienkev
    Haven't had the chance to check other post yet.
    best get cracking, then


    Quote Originally Posted by obrienkev
    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


    Quote Originally Posted by obrienkev
    Should date/time always be entered in this format..YYYY-MM-DD
    not necessarily, but it is a format that always works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •