date format

Question

Post reply

date format

John-238382

Old Hand

Points: 380
More actions
October 16, 2005 at 12:08 am

#91430

Is there a way to trim the date of it's time part but still keep the date format.
In other words this column's values look like this:
2005-10-02 13:31:00
I want to create another table populated with only the date part (2005-10-02) from this column. I want the column in the new table to be in date format as well, so that I can do other manipulations with it.

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

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

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 1

Use the convert function. Look up BOL for the syntax and the different options available.

John-238382 Old Hand Points: 380 More actions · Answer 2

ok, I checked BOL for Convert and Cast functions. They allow me to covert one datatype to another. However there is no "date-only" datatype in SQLServer that I am aware of. Are you suggesting I create a user-defined datatype, which would be date-only? But I don't know if I would still be able to use different date functions with this datatype...

I have no problem converting my field to characters and parsing it (using Datepart function), but I would to keep it in some kind of Date format so that I could use date functions for it)

Thank you.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

Look closer at Convert. There is a third parameter which is a date format.

Here is an example:

declare @date varchar(10)
set @date = convert(varchar,getDate(),121)
print @date

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

John-238382 Old Hand Points: 380 More actions · Answer 4

It's a nice way to convert my dates into a desired character format. However, my question is about achieving this date-only format WITHIN the database. Like a "DATE", not DATETIME datatype...

Thanks anyway.

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 5

You can still have the data-type as datetime (or smalldatetime) but store only the "date" portion in it..i.e. prior to writing to the database, the client application makes the necessary changes using the convert function and stores it that way so that the time portion is 00:00:00.000.

That is one way to do this...or, if you do not want to do this at the time of writing the data, then while reading the data, you can do the conversion as you need/apply the date functions as you need. If you do not want to change the client application to do this, you can also have a computed column which is computed after the convert function gets applied so that the computed coluimn stores only the date part.

Hth

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 6

OK, use my explanation of convert to store the date with the time zeroed out in your insert or update statement.

insert into table2 (newDate)
select convert(varchar,oldDate,121)
from table1

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

John-238382 Old Hand Points: 380 More actions · Answer 7

Thanks a lot people! I was pondering over these different approches and it's encouraging to hear that they are all legit.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

Just a small correction to the convert statement. (ie never rely on implicit default behaviours)

select convert(varchar(10),oldDate,121)

..while I'm at it, the above is 99% foolproof, though the format yyyy-mm-dd is language dependant. Should you desire to be 100% absolutely waterproof when writing datestrings into datetime datatypes, you should only use yyyymmdd -

select convert(varchar(8),oldDate,112)

/Kenneth