datetime

  • can anyone please tell how to format datetime of the output in select statement to show just the date and not time

    date output is 2005-01-28 15:00:00

    I want just the date 2005-01-28 and not the above

    Thanks a lot

  • Using getdate() as an example date:

    select cast(floor(cast(getdate() as float)) as datetime)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Try this

    CONVERT(Varchar(12),GetDate(),101)

    Don't forget to change date type for other countries

    REGARDS

    Don

     

  • Hi,

    try this -

    CONVERT(VARCHAR, YourDateField, 111)

    HTH.

  • I wrote the following for internal use.  It just puts all the date format material from the books online together.

    Joachim.

    MS SQL Server (Using Convert)

    Date -> formatted string

    convert (varchar, field ,105)

     

    Example: DD-MM-YYYY

     

    select convert (varchar, a1,105) from a

     

    Formatted string -> date

     

    insert into a select  convert (datetime, '30-1-2000' , 105)

     

     

    Example: DD-MM-YYYY

     

    insert into a convert (datetime, '30-1-2000' , 105)

     

    Formats

     

    Without century (yy)

    With century (yyyy)

    Standard

    Input/Output**

    -

    0 or 100 (*)

    Default

    mon dd yyyy hh:miAM (or PM)

    1

    101

    USA

    mm/dd/yy

    2

    102

    ANSI

    yy.mm.dd

    3

    103

    British/French

    dd/mm/yy

    4

    104

    German

    dd.mm.yy

    5

    105

    Italian

    dd-mm-yy

    6

    106

    -

    dd mon yy

    7

    107

    -

    Mon dd, yy

    8

    108

    -

    hh:mm:ss

    -

    9 or 109 (*)

    Default + milliseconds

    mon dd yyyy hh:mi:ss:mmmAM (or PM)

    10

    110

    USA

    mm-dd-yy

    11

    111

    JAPAN

    yy/mm/dd

    12

    112

    ISO

    yymmdd

    -

    13 or 113 (*)

    Europe default + milliseconds

    dd mon yyyy hh:mm:ss:mmm(24h)

    14

    114

    -

    hh:mi:ss:mmm(24h)

    -

    20 or 120 (*)

    ODBC canonical

    yyyy-mm-dd hh:mi:ss(24h)

    -

    21 or 121 (*)

    ODBC canonical (with milliseconds)

    yyyy-mm-dd hh:mi:ss.mmm(24h)

    -

    126(***)

    ISO8601

    yyyy-mm-dd Thh:mm:ss.mmm(no spaces)

    -

    130*

    Hijri****

    dd mon yyyy hh:mi:ss:mmmAM

    -

    131*

    Hijri****

    dd/mm/yy hh:mi:ss:mmmAM

     

    Look at  the next chapter, "MS SQL Server (Using date functions)" if the preferred format is not here.

    Reference

    SQLServer Books Online

    MS SQL Server (Using date functions)

     

    Convert is limited to the above styles.

     

    For a select there is an alternative in the function datepart

     

    Date -> formatted string

     

    A datefunction gives a number for part of the date

    str ( float_expression [ , length [ , decimal ] ] )  turns a number into a string

    replace(string1,' ','0') turns the space in one character months and days into a 0

     

     

    Example:

    YYYY-MM-DD

     

    select replace(str(year(a1),4) + '-' +str(month(a1),2) + '-' + str(day(a1),2),' ','0') from a

     

     

    Formats

     

    Function

    description

    Day(datefield)

    day of month

    month(datefield)

    month as number

    year(datefield)

    year  as number

    datepart(format,datefield)

    part of date as number

    datename(format,datefield)

    part of date as string

     

    Datepart formats:

    Datepart

    Abbreviations

    year

    yy, yyyy

    quarter

    qq, q

    month

    mm, m

    dayofyear

    dy, y

    day

    dd, d

    week

    wk, ww

    weekday

    dw

    hour

    hh

    minute

    mi, n

    second

    ss, s

    millisecond

    ms

    Datename formats:

    Datepart

    Abbreviations

    year

    yy, yyyy

    quarter

    qq, q

    month

    mm, m

    dayofyear

    dy, y

    day

    dd, d

    week

    wk, ww

    weekday

    dw

    hour

    hh

    minute

    mi, n

    second

    ss, s

    millisecond

    ms

    The main difference between  datepart and datename is that month becomes the name of the month in datename

     

    Reference

    SQLServer Books Online

  • Hi,

    Good discussion. Now, what is everyone's opinion on how it is better to compare dates, for exaple to select all records for today? There are 2 ways to do that, first by comparing strings and the second one by comparing 3 values. There is a third one too when you say that a date is more then yesterday and less then tomorrow, I use it sometimes too.

    So what is more dependable?

    1. Convert to string both the DateTime field value and GetDate() as was discussed in the previous posts using Convert with formats and compare those strings

    2. I usually use the following:

    Where Day(Mydate) = Day(GetDate())

    AND Month(Mydate) = Month(GetDate())

    AND Year(Mydate) = Year(GetDate())

     

     

    Regards,Yelena Varsha

  • 1) WHERE (DATEDIFF(d, datelogged, GETDATE()) = 1)

    2) WHERE (despatchdate BETWEEN '" & convDate(dteFromDate) & "' AND '" & convDate(dteToDate) & "')

    'Using function to massage date.

    3) WHERE orderdate > Getdate() - 3

     

     

     

     

  • Dgrover,

    I do sometimes use your #3 Getdate() - 3

    what about #2? the ConvDate function? it is not SQL function. I think it is Unix or something unless you have written your own and want to share with us.

    I also found a good ASP VBScript function named ConvDate in the article

    ASP Date/Time Formatting

    http://www.pbdr.com/vbtips/asp/dateformat.htm

    They say we may copy and paste this script for our own use.

    Yelena

    Regards,Yelena Varsha

  • This is the convDate function I use it when a user is typing in a date .

      <%

      'This function recieves a date from text string in format dd/mm/yy or dd/mm/ccyy

      'And creates a string that is compatible with inserting into sql as a datetime field

      'If an empty string is passed it just passes back trimmed original

      'Write Value Test value to sql database 'datetime' field

      'Added 16/04/2003

      'If a 2 digit year is passed then 20 is prepended onto year to build a CCYY year

      '---------

      'sValues =   " NULLIF('" & convdate(sDate) & "','')"

      '---------

      'pass a date as dd/mm/yy

        Function convDate(theDate)

          Dim Itemp

            If TRIM(theDate) <> "" Then

              sTemp = cdate(theDate)

              dteArray = Split(sTemp,"/",-1,1)

                If LenB(dteArray(2)) = 2 Then

                  dteArray(2) = "20" & dteArray(2)

                End If

              convDate =dteArray(2) & "/" & dteArray(1) & "/" &  dteArray(0)

            Else

              convDate = Trim(theDate)

            End If

        End Function

      %>

  • dgrover,

    This is a good function. I will save it for the conversion from string to date, very helpful.

    I probably have just to create a user-defined function for stripping time out of datetime field in SQL, using any of the techniques discussed above.

    Thanks.

    Yelena

     

    Regards,Yelena Varsha

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply