Hi there,
See if you can help me with the following:
I need to write an SQL code that will return me:
The 1st day & the Last day of the Previous Month in the following format
(smalldatetime):
yyyy-mm-dd hh:mi:ss (24h)
Regards,
--
Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums...neral/200507/1 5 5739
If you don't have a calendar table (which can be very helpful for
situations like this), you can do it on-the-fly using SQL Server like
so:
SELECT DATEADD(m, -1, FirstOfCurrentMonth) as FirstOfPreviousMonth,
DATEADD(d, -1, FirstOfCurrentMonth) as LastOfPreviousMonth
FROM (SELECT CONVERT(smalldatetime,DATENAME(m, GetDate())
+ ' 1,' + DATENAME(yyyy, GETDATE())) as FirstOfCurrentMonth) a
Of course, you'll want to do the formatting on the client side.
HTH,
Stu
On Wed, 20 Jul 2005 16:56:42 GMT, Ray via SQLMonster.com wrote: Hi there,
See if you can help me with the following:
I need to write an SQL code that will return me:
The 1st day & the Last day of the Previous Month in the following format (smalldatetime):
yyyy-mm-dd hh:mi:ss (24h)
Regards,
declare @d1 smalldatetime
declare @d2 smalldatetime
declare @d3 smalldatetime
-- @d1 is the input date
set @d1 = CURRENT_TIMESTAMP
-- truncate hours, min, etc.
set @d1 = convert(smalldatetime, floor(convert(float, @d1)))
-- @d2 - last day of previous month
set @d2 = dateadd(day, - datepart(day, @d1), @d1)
-- @d3 - first day of previous month
set @d3 = dateadd(day, - datepart(day, @d2) + 1, @d2)
Hi Stu,
Thank you very very much ..... it was a great help.
Best regards,
Stu wrote: If you don't have a calendar table (which can be very helpful for situations like this), you can do it on-the-fly using SQL Server like so:
SELECT DATEADD(m, -1, FirstOfCurrentMonth) as FirstOfPreviousMonth, DATEADD(d, -1, FirstOfCurrentMonth) as LastOfPreviousMonth FROM (SELECT CONVERT(smalldatetime,DATENAME(m, GetDate()) + ' 1,' + DATENAME(yyyy, GETDATE())) as FirstOfCurrentMonth) a
Of course, you'll want to do the formatting on the client side.
HTH, Stu
--
Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums...neral/200507/1
Build a calendar table with all the temporal information you need
instead of trying to compute it on the fly. Next, the display of
temporal data has nothing to do how it is stored.
Ross,
Thank you so much ....
Ross Presser wrote: Hi there, [quoted text clipped - 8 lines] Regards,
declare @d1 smalldatetime declare @d2 smalldatetime declare @d3 smalldatetime
-- @d1 is the input date set @d1 = CURRENT_TIMESTAMP -- truncate hours, min, etc. set @d1 = convert(smalldatetime, floor(convert(float, @d1)))
-- @d2 - last day of previous month
set @d2 = dateadd(day, - datepart(day, @d1), @d1)
-- @d3 - first day of previous month
set @d3 = dateadd(day, - datepart(day, @d2) + 1, @d2)
--
Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums...neral/200507/1 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Keith |
last post by:
I have downloaded some sample code from the net to display a calendar etc.
The code is below.
How can I modify this code so that when called in a popup window and a date
is clicked, that date...
|
by: Killer |
last post by:
How can i calculate the last day of the previous month?
Help me,please
|
by: SimonC |
last post by:
I would like to return data from the last 2 weeks of each given month
in Javascript, but in 2 formats.
So, the penultimate week (Monday to Sunday) and the last week (Monday
to ??)
I'm not...
|
by: Burghew |
last post by:
Thanks for the previous replies.....
What I need is to generate invoices for customers whom we rent out
equipment.
I will be choosing the date and year say for December 2005 print the
invoices...
|
by: larry |
last post by:
I am in the process of rewriting one of my first PHP scripts, an event
calendar, and wanted to share the code that is the core of the new
calendar. My current/previous calendar processed data...
|
by: Kajsa Anka |
last post by:
Before I re-invent something I would like to ask if there exists some code
that can be used for create the HTML code for a calendar which I then can
include on a web page.
The module in the...
|
by: Vijay |
last post by:
Prep Courses for International Certifications, CSTE & CSQA & ISEB &
ISTQB &Business Analyst & SOA Certifications in HYDERABAD.
After receiving overwhelming response to our last 50+ batches, ...
|
by: brymcguire |
last post by:
Hi,
I have a requirement to design a query that identifies items sold
between two dates. There is a 'SoldDate' datetime field used to
register what date the item was sold.
The query needs...
|
by: guyborn |
last post by:
I have been trying to get data from the database from the of the previous month to the second last week of the current month.I only managed to get data from the previous month to today's date.
...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |