Help with a query

Question

Post reply

Help with a query

xxxxxx

Ten Centuries

Points: 1080
More actions
February 6, 2011 at 8:50 pm

#231048

Can someone tell me how to chain queries together. Im using this query I found on the net
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))
to get the first day of the previous month. im running a job that uses a query to get the first day on the pervious month (see above) but then it need to be converted to epoch time to be used to query the table.
Im having isses getting them to work together.

Viewing 15 posts - 1 through 15 (of 15 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

So post your other code so we can see how the two should be "combined".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 2

As Jeff said, post both queries (with comments on what each is supposed to do) so we can help you combine them together or offer other advice.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

xxxxxx Ten Centuries Points: 1080 More actions · Answer 3

ok, first I am using this query to get first day of the previous month:

SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))

Then I take the result and convert it to epoch time suing this query:

SELECT DATEDIFF(s, '1970-01-01 00:00:00', time field)

*Time field would be the result of the first query

Then after I get resutl of converting to epoch time I use this value in a query to select info where column "startdate" is equal to or greater than the converted epoch time.

So how do i combine the 2 queries to get the date, covnert to epoch and then plug that into my select query.

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

lance.kentwell (2/8/2011)
ok, first I am using this query to get first day of the previous month:
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))
Then I take the result and convert it to epoch time suing this query:
SELECT DATEDIFF(s, '1970-01-01 00:00:00', time field)
*Time field would be the result of the first query
Then after I get resutl of converting to epoch time I use this value in a query to select info where column "startdate" is equal to or greater than the converted epoch time.
So how do i combine the 2 queries to get the date, covnert to epoch and then plug that into my select query.

I'm not sure about the first SELECT query, looks like it is mixing date time parts.

Check out this blog post, Some Common Date Routines.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

{edit} Made a mistake... I'll be back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

lance.kentwell (2/8/2011)
ok, first I am using this query to get first day of the previous month:
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))
Then I take the result and convert it to epoch time suing this query:
SELECT DATEDIFF(s, '1970-01-01 00:00:00', time field)
*Time field would be the result of the first query
Then after I get resutl of converting to epoch time I use this value in a query to select info where column "startdate" is equal to or greater than the converted epoch time.
So how do i combine the 2 queries to get the date, covnert to epoch and then plug that into my select query.

Ok... after taking another look at that code, I first need to know what the real intent of the first bit of code was. It correctly calculates the first of the previous month and then you add zero seconds to it. Is that what you meant to do or is it a bit of phat phingering?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

xxxxxx Ten Centuries Points: 1080 More actions · Answer 7

I figured it out 🙂

SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))))/86400

SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))))/86400

I also added "/86400" on the end to convert the whole tings to days since epoch. Works perfectly for my purposes.

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

I come up with something like this:

SELECT DATEDIFF(s, '1970-01-01 00:00:00', dateadd(mm, datediff(mm, 0, GETDATE()) - 1, 0));

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

lance.kentwell (2/8/2011)
I figured it out 🙂
SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))))/86400
SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))))/86400
I also added "/86400" on the end to convert the whole tings to days since epoch. Works perfectly for my purposes.

Want days instead of seconds? How about this instead:

SELECT DATEDIFF(dd, '1970-01-01 00:00:00', dateadd(mm, datediff(mm, 0, GETDATE()) - 1, 0));

xxxxxx Ten Centuries Points: 1080 More actions · Answer 10

Lynn Pettis (2/8/2011)
lance.kentwell (2/8/2011)
I figured it out 🙂
SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))))/86400
SELECT DATEDIFF(s, '1970-01-01 00:00:00', (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))))/86400
I also added "/86400" on the end to convert the whole tings to days since epoch. Works perfectly for my purposes.
Want days instead of seconds? How about this instead:
SELECT DATEDIFF(dd, '1970-01-01 00:00:00', dateadd(mm, datediff(mm, 0, GETDATE()) - 1, 0));

Even better, that gives me first day of the previous month, any idea on how to get the last day of the previous month using this method?

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

I could, but a better way is to ask you to check out that blog I suggested. Look at the functions provided and see if you can figure out how to cme up with the last day of last month.

Hint: It is the the day before the first day of the current month.

Let us know what you come with for this requirement.

xxxxxx Ten Centuries Points: 1080 More actions · Answer 12

ok im all sorted out now working just how i want it.

I had another requirment come up last night however and try as I did I couldnt figure it out. What I need to do is get just the month and year for the previous month. e.g. I do a query today 10th Feb 2011 I need the result to be 02/2011, Jan 2011 etc. Im not sure what formats I can have the result in so I'll take whatever I can get 🙂

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 13

So we can see what you've done, please post your solution to your other problem.

There are several ways to solve your second problem. Because we're trying to help you learn the code, we're not going to give you the solution. Instead, I'm going to give you a list of items to lookup in Books Online. You do not have to use all of them to get to your solution. In fact, you'll probably only use some of them, but as they all contribute to different possible solutions, I'll give them all to you.

Substring function

Convert function

variables

dates [SQL Server]

DATE function

functions [SQL Server], dates

DatePart function

Have fun.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

xxxxxx Ten Centuries Points: 1080 More actions · Answer 14

I get your trying to teach me, I wish i had more time to dedicate to learning SQL more. But I think i figured it out anyway. Im sure there is a cleaner way of doing this but here is what I got

SELECT YEAR(GETDATE()) as "Year";

SELECT MONTH(DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))) as "Month";