DateTime Data Type

Question

DateTime Data Type

lmod

SSC Veteran

Points: 246
More actions
June 27, 2005 at 11:59 pm

#167309

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

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

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

Meghana SSC Eights! Points: 840 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

lmod SSC Veteran Points: 246 More actions · Answer 3

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.

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

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.

lmod SSC Veteran Points: 246 More actions · Answer 5

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

ron k SSCertifiable Points: 6103 More actions · Answer 6

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

it would look something like this :

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

lmod SSC Veteran Points: 246 More actions · Answer 8

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

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