June 30, 2009 at 10:58 am
I am trying to convert a date field to last day of previous month but don't know how to drop the time. Here is what I am using:
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,enddate),0)))
from pvtransmaster
where enddate > '2009-06-29'
or
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,getdate()),0)))
and getting the following results from my first query:
2009-05-31 23:59:59.997
2009-05-31 23:59:59.997
2009-05-31 23:59:59.997
What I really need is:
05/31/2009
Any help would be appreciated.
June 30, 2009 at 11:02 am
Give this a try: select dateadd(mm, datediff(mm, 0, getdate()), -1)
June 30, 2009 at 11:06 am
That works for the date as 05/31/2009 but it also gives me the time of 00:00:00.000 and I only need 05/31/2009.
Thanks, any other ideas?
June 30, 2009 at 11:08 am
Sorry, but that doesn't work as I need the format to be last day of previous month in MM/DD/YYYY format.
June 30, 2009 at 11:13 am
Why? What is the data type of the column you will be comparing the value with?
June 30, 2009 at 11:16 am
I need to export fields to a flat file to import into a billing system. Thanks for your help but I just realized that we have a function in our database I can use to format the date field. It is as follows:
select dbo.fnformatdate (DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))), 'MM/DD/YYYY')
The field is called enddate and is a datetime.
Thanks for the help.
June 30, 2009 at 11:20 am
First, no reason to convert to mm/dd/yyyy when comparing to a datetime column as SQL Server will just convert the character representation back to a datetime value.
Second, this will probably be faster if you must have it in a character format: convert(char(10), dateadd(mm, datediff(mm, 0, getdate()), -1), 101)
It won't have the overhead of calling your user-defined function.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply