User Defined Date Function

Question

User Defined Date Function

john.imossi

SSC Rookie

Points: 30
More actions
September 1, 2009 at 2:23 am

#204177

I've created a date range within the WHERE clause:
WHERE Date Between CONVERT(DateTime, '01-JAN-2008 00:00:00', 103)
AND CONVERT(DateTime, '31-DEC-2008 23:59:59', 103)
However the 'DD-MON-YYYY HH:MM:SS' is something I'd like to replace with something like @FromDate & @ToDate, so that a user can input a date rangeo f their own. Would anyone be able to help me with this please?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

john.imossi (9/1/2009)
I've created a date range within the WHERE clause:
WHERE Date Between CONVERT(DateTime, '01-JAN-2008 00:00:00', 103)
AND CONVERT(DateTime, '31-DEC-2008 23:59:59', 103)
Thanks

Not sure if this is what you were looking for,

Create a stored procedure, pass two parameters, @FromDate, @toDate.

Put the variables in the CONVERT part,

CONVERT(DateTime, @FromDate, 103)

hope this helps.

Sorry I did not see the heading, you said u need a UDF, u can follow the same approach i guess.

---------------------------------------------------------------------------------

john.imossi SSC Rookie Points: 30 More actions · Answer 2

I've had a go and got something like this:

DESCLARE @FromDate VARCHAR(20), @ToDate VARCHAR(20);

SELECT *

FROM Table

WHERE Date BETWEEN CONVERT(DateTime, @FromDate, 103)

AND CONVERT(DateTime, @ToDate, 103)

This runs but it returns no data, I'm surprised it will run at all to be hoenst. When I used to create something similar to this in Oracle it would bring up an input box that the user could write the variables into the SQL. Can I get this to happen in SQL Server?

Thanks

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

john.imossi (9/1/2009)
This runs but it returns no data, I'm surprised it will run at all to be hoenst. When I used to create something similar to this in Oracle it would bring up an input box that the user could write the variables into the SQL. Can I get this to happen in SQL Server?
Thanks

Dont take this the wrong way, but it sounds as if you need a lot more training.

Maybe you should be googling for tutorials ?

Clear Sky SQL
My Blog[/url]

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

I know! In Toad you will be prompted with an input box to enter the values.I am not sure if it is there in SSMS. Lets see if anyone else has the answer. You enter some date values to the variables like this (after declaring) and then execute, u might get some records?

SET @fromdate = '1-Jan-2009'

---------------------------------------------------------------------------------

john.imossi SSC Rookie Points: 30 More actions · Answer 5

Mr. Ballantyne, you are right of course, I do need more training, but please do not impugn my intelligence by suggesting I am currently not going through tutorials or looking for them where necessary. However I thought that I would also seek the knowledge of those who are more experienced than myself and ask their advice. If you decide to reply in this thread again with another comment, I would appreciate it if you would also include a link to a tutorial that might be useful or pertinent, as that would be far more useful than your previous post.

Pakki, thanks for your suggestions, yes TOAD does and I shall keep looking.

Thanks

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Dave Ballantyne (9/1/2009)
Dont take this the wrong way

Obviously you did take it the wrong way, pardon me for attempting to find out what your level of knowledge is.

Clear Sky SQL
My Blog[/url]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

john.imossi (9/1/2009)
I've had a go and got something like this:
DESCLARE @FromDate VARCHAR(20), @ToDate VARCHAR(20);
SELECT *
FROM Table
WHERE Date BETWEEN CONVERT(DateTime, @FromDate, 103)
AND CONVERT(DateTime, @ToDate, 103)
This runs but it returns no data, I'm surprised it will run at all to be hoenst. When I used to create something similar to this in Oracle it would bring up an input box that the user could write the variables into the SQL. Can I get this to happen in SQL Server?
Thanks

Why not use the DATETIME datatype instead of VARCHAR. Then you don't have to mess with the CONVERT functions. However, just a side note, CONVERT is much more presentation. CAST is the preferred function for data conversions within code (although, as with everything else, there are exceptions).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 8

Sql server wont prompt for variable value if it is not assign on time of query execution, just like oracle (or access). You need to initialize them by ur own. something like this,

DECLARE @FromDate VARCHAR(20), @ToDate VARCHAR(20);

SET @fromdate = '1-Jan-2009';

SET @Todate = '31-Mar-2009' ;

SELECT *

FROM Table

WHERE Date BETWEEN CONVERT(DateTime, @FromDate, 103)

AND CONVERT(DateTime, @ToDate, 103)

"Don't limit your challenges, challenge your limits"

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

If you write a stored procedure with parameters, you can right click on the procedure in SMSS and select the Execute Stored Procedure option. You will be prompted for all input parameters.

If this helps, try thinking of the database as being separate from the application programs which connect to it. The database simply delivers data. The application program provides services such as prompting. Both SMSS and what you think of as Access are applications.

John, if you can, please be forgiving about both the question and suggestion which you found so offensive. I'm sure no insult was intended. But it is helpful to know the level of expertise behind a question in order to avoid misunderstandings. I misunderstood your original post until I saw the reference to Access, and fortunately I'm familiar with how it prompts for null variables. This is not a reflection on either your intelligence or mine. 😉

Best of luck to you.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Dave Ballantyne (9/1/2009)
john.imossi (9/1/2009)
This runs but it returns no data, I'm surprised it will run at all to be hoenst. When I used to create something similar to this in Oracle it would bring up an input box that the user could write the variables into the SQL. Can I get this to happen in SQL Server?
Thanks
Dont take this the wrong way, but it sounds as if you need a lot more training.
Maybe you should be googling for tutorials ?

+1. Time to read some books and/or take a class or two. Hunting and pecking on a forum is a truly inefficient way to learn large amounts of material! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service