Converting date time

Question

Post reply

Converting date time

tschuler-738392

Ten Centuries

Points: 1172
More actions
June 30, 2009 at 10:58 am

#135850

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.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Give this a try: select dateadd(mm, datediff(mm, 0, getdate()), -1)

tschuler-738392 Ten Centuries Points: 1172 More actions · Answer 2

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?

tschuler-738392 Ten Centuries Points: 1172 More actions · Answer 3

Sorry, but that doesn't work as I need the format to be last day of previous month in MM/DD/YYYY format.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Why? What is the data type of the column you will be comparing the value with?

tschuler-738392 Ten Centuries Points: 1172 More actions · Answer 5

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.

Converting date time

Cookies on SQLServerCentral