Date Function Question. - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Date Function Question.

Author

Topic

Tristan
Starting Member

5 Posts

Posted - 2007-05-14 : 12:07:20

Hey,

I'm currently trying to see if there's a date function to know what the LAST day of the month is. If I'm not being specific enough, please let me know.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 12:10:12

select dateadd(month, datediff(month, -1, current_timestamp), -1)

Peter Larsson
Helsingborg, Sweden

Tristan
Starting Member

5 Posts

Posted - 2007-05-14 : 12:13:13

You rock, thank you, Peso.

Tristan
Starting Member

5 Posts

Posted - 2007-05-14 : 12:33:09

Stupid question, is there a way to rid the results of the time and just give the date format? I tried current_date but it gave syntax errors.

Kristen
Test

22859 Posts

Posted - 2007-05-14 : 12:44:11

SQL Server only has a composite Date + Time format.

A "time" of 00:00:00.000 is deemed to be midnight, and as such "just the date"

You can format the output by converting to a string, e.g.

SELECT CONVERT(varchar(11), dateadd(month, datediff(month, -1, current_timestamp), -1) , 113) -- For other conversion formats see BoL

but you would be well advised to do this in your application instead - so that the data type transmitted to your application is "date / time", rather than "string".

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-15 : 09:18:49

<<
but you would be well advised to do this in your application instead - so that the data type transmitted to your application is "date / time", rather than "string".
>>

I agree with you

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-05-15 : 10:45:21

I don't like this trend of "Lets explain it to death". I like the old school "Format it in the front end" response better

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-05-15 : 19:25:06

I like the old school response of "IF you have a front end, it should be done there, instead".