A function to return date only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A function to return date only

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2007-05-03 : 11:53:42

How to code a function to input date time data and return date only?
For example, if pass [Birth Date] = '5/1/1955 12:00 AM' to this function will return '5/1/1955' only.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-03 : 11:55:43

dateadd(day, datediff(day, 0, @date), 0)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-03 : 11:56:24

have you tried the CONVERT/CAST functions?


declare @d datetime
set @d = '5/1/1955 12:00 AM'
select converT(varchar,@d , 101)

Check out CONVERT function in BOL. There are other date formats available too.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-03 : 11:57:00

also see http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-03 : 11:58:04

Do you want this for purely formatting purpose? Then do it in the front-end. Otherwise, follow KH's solution.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 12:47:12

quote:
Originally posted by dinakar

have you tried the CONVERT/CAST functions?

declare @d datetime
set @d = '5/1/1955 12:00 AM'
select converT(varchar,@d , 101)

Check out CONVERT function in BOL. There are other date formats available too.

As mentioned over and over here in the forums, that does NOT return a date. The concept of datatypes is extremely important in a relational database; your SQL statements and expressions should be dealing with data of the proper type; converting dates to VARCHAR's causes issues with comparing, sorting, date functions, etc, as well as when the VARCHAR's are returned to the clients that really want actual dates to do the comparing, sorting, date functions, and formatting ... Are you not aware of this? I only ask because I assume an MVP would endorse best practices as well as using proper data types at the database layer, not to be rude ....

EDIT: fixed some typos

- Jeff
http://weblogs.sqlteam.com/JeffS

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-03 : 12:59:29

Jeff
I know/understand that using a CONVERT function changes the datatype to varchar. Not knowing what the user is doing, how much of assumption can you make? If all the user is doing is to show the value in a report, it doesnt matter if the datatype is changed. As much important it is to understand this difference, sometimes its still easy to just use the damn CONVERT for simple display purposes.

Note:
----
Do you guys have anything against MVP's? I have a feeling people here are somehow uptight about it. I mean whats the big deal? relax..

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 14:07:27

>>do you guys have anything against MVP's? I have a feeling people here are somehow uptight about it. I mean whats the big deal? relax..

No, not me anyway, absolutely not! I just bring it up as an indicator of your experience with SQL Server.

>>I know/understand that using a CONVERT function changes the datatype to varchar. Not knowing what the user is doing, how much of assumption can you make?

It's irrelevant. they asked how to return a date. CONVERT doesn't return a Date. And even if they WANT to convert their date to a VARCHAR, it should be pointed out to them what the best practices are and what the pro's and cons of doing that are. How many times do we see "why don't my dates sort properly??" questions because people are converting dates to VARCHARs for display purposes.

>>If all the user is doing is to show the value in a report, it doesnt matter if the datatype is changed.
>>As much important it is to understand this difference, sometimes its still easy to just use the damn CONVERT for simple display purposes.

Again, no offense, but that is wrong. If the report expects a dateTime, the report cannot do anything with that VARCHAR unless it converts it BACK to a datetime, at which point date formats suddenly become very important, when they shouldn't even be a factor. What if the clients uses MM/DD/YYYY to convert strings to dates, but SQL returns the dates as DD/MM/YYYY ?

And that is my entire point, it is NOT easier to do a "damn convert" for display purposes, for all of these reasons. It might seem simpler, but you are now adding conversions and date formats into the equation when all that SQL Server needs to do is "return the damn date" -- which is all the client applications need and EXACTLY what they want and expect, where they are infinitely more capable of formatting those dates EXACTLY in any way necessary.

otherwise, if you think SQL Server should be formatting and converting dates and money values and bits and returning nothing but VARCHARS in every column, you end up with this scenario: http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60145.aspx (that's satire, but it happens when DBA's don't understand the importance of the difference between the data layer and presentation layer).

- Jeff
http://weblogs.sqlteam.com/JeffS

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2007-05-03 : 14:16:54

Posted - 05/03/2007 : 14:15:57
--------------------------------------------------------------------------------

I use code:

dateadd(day, datediff(day, 0, @date), 0)

to display date only. It works fine in SQL 2000.

But, in SQL 2005, it coming "12:00 AM" as well.

How to fix it without time part but still keep datetime data type?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-03 : 14:17:22

quote:
Originally posted by jsmith8858
Again, no offense, but that is wrong. If the report expects a dateTime, the report cannot do anything with that VARCHAR unless it converts it BACK to a datetime, at which point date formats suddenly become very important, when they shouldn't even be a factor. What if the clients uses MM/DD/YYYY to convert strings to dates, but SQL returns the dates as DD/MM/YYYY ?

It is not wrong to use the convert if you know the value is converted. There can be so many "What if.."s. So unless we understand the entire end to end scenario we dont know which solution is best. Its a good thing to see various ways of resolving an issue.
The only thing I overlooked was not mentioning that the value would get CONVERTed to varchar for which I stand corrected. I shouldnt have been lazy.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 14:21:22

quote:
Originally posted by Sun Foster

Posted - 05/03/2007 : 14:15:57
--------------------------------------------------------------------------------

I use code:

dateadd(day, datediff(day, 0, @date), 0)

to display date only. It works fine in SQL 2000.

But, in SQL 2005, it coming "12:00 AM" as well.

How to fix it without time part but still keep datetime data type?

that is a beautiful follow up to our discussion. A DateTime value ALWAYS has a time, that's why it is called datetime. Do not worry about what Query Analyzer or Management Studio displays when writing your code, worry about the data itself. Your front end -- reporting tool, client application, web page, etc -- can format that DateTime any way that it wants very easily with great flexibility. What front end are you using? Where is this data ultimately displayed?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources