DateTime Data Type

  • Is it possible to use the datetime data type with out the forced time input?

  • The time will always be stored as a part of the datetime datatype AFAIK. But you can use date functions to get the data in the format you want.

    Please refer to the BOL for date functions.

    Regards

    Meghana

     


    Regards,

    Meghana

  • What are you trying to do exactly?  It is possible to trim the time bit from a date before storing it in SQL Server, if that is your requirement.

    Regards

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • That is exactly what I want to do. I want to store a date in proper date format so that I can later perform date specific calcs in my app code, and was trying to eleminate the need to truncate the time stamp in either the app code or transaction SP that I have to create. I was thinking that I could just use a varchar data type and force validation for correct format in the UI but my lack of knowledge with SQL has me thinking that I need the proper date data type so I can perfoem date specific calculations in my app code. Is it possible to store the date data in a varchar data type and in the SP convert it to a date format before it is sent to the app? Or is there a better way to achieve the desired result without un-neccessary scripting in the SP or app code via RegExp etc.

  • I would recommend you store the data as smalldatetime or datetime.  You may want to inevitably do calculations on the SQL Server side as more data is entered into your system. 

    Dates can be a real booger to work with in SQL Server, (I think Yukon is making a date datatype without the time, but SQL Server still has the old style).  I would recommend you read up BOL about the date datatypes, (I found the Microsoft SQL Server books give a much fuller explanation than BOL, but that is up to you if you want to buy a book and go through it). 

    For presentation, you can try the following, (just insert your date field for the hardcoded date and see what you get). 

    SELECT CONVERT( smalldatetime, '2005-06-28')

    SELECT CONVERT( datetime, '2005-06-28')

    SELECT CONVERT( varchar, CONVERT( smalldatetime, '2005-06-28'), 101)

    SELECT CONVERT( varchar, CONVERT( datetime, '2005-06-28'), 101)

    The 101 refers to the presentation format and should be in BOL. 

    I would also recommend reading Frank Kalis's on various date examples.  He really knows this stuff in and out...  

    I wasn't born stupid - I had to study.

  • Thanks for your help Farrell, I will follow your suggestions.

  • You can (should) place a constraint on the column to ensure that the time portion is always 00:00:00.000.  Then it will never be an issue.

  • it would look something like this :

    dateadd(D, 0, Datediff(D, 0, DateCol)) = DateCol

  • Are you referring to how the Constraint would look that Ron K stated that I should use?

  • Yes, the date functions are stripping the time part using mathematical operations, which is faster than any cast you can come up with.

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

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