Help with Dates??

[Woolers]

Hi All - I have the following code as part of a query string on a database:

Request.LoggedDate >= { ts ' 2008-07-30 00:00:00'} And Request.LoggedDate <= { ts ' 2008-07-30 23:59:59'}

However, the date/time value has to ben entered manually.
Is there a way that the value of this between the single quote marks can be set so that we don't have to enter the values manually??

Also - I have the following string as part of another query:
@BeginDate = 'Jul 30 2008 12:00:00:000AM', @EndDate = 'Jul 30 2008 11:59:59:000PM'

It would be nice if this one could be automated also but the date/time format is different.

Any suggestions would be most appreciated.

Thanks

I can provide full code if necessary..

Keyboard Not Detected.....
Press F1 to Continue.
:}

 

[RiverGuy]

I don't understand what you are wanting to do. Are you wanting to have one date input instead of two, the end date being calculated automatically from the start date? To get your same end date, you can take the start date, add a day with the DATEADD function, subtract a second with the DATEADD function, assign it to an internal variable, and you should have your 11:59:59

 

[Woolers]

RiverGuy,

Unfortunately the query needs to follow the format above but I could probably substitute having to type in the values manually if I could get something in between the quotes...

i.e. at present it states
{ ts ' 2008-07-30 00:00:00'}

Now I know I can use the following statement to get today's date value

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]

but how do I go about putting the date bit in the middle of the ' 's i.e.

{ ts ' SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]' 00:00:00'}

Is abviously wrong!! - I'm very new to SQL & am not sure how to get this to work as yet.

Hope it's a little bit clearer??

Many Thanks

Keyboard Not Detected.....
Press F1 to Continue.
:}

 

[RiverGuy]

Are you using Crystal Reports or something?

 

[Woolers]

No it's a proprietary helpdesk system based on SQL...

The people that develop it won't help us, as they say the SQL can't be done manually but I know enough to know I can run the manual query in the software & use profiler to record the SQL which is where I got it in the first place

I can do this in VBScript no problem at all & have done already, problem is, I'd rather have a SQL script to do it instead...

Any suggestions welcome, if it can't really be done this way then I'll have to leave it & stick with VB instead.

Thanks

Keyboard Not Detected.....
Press F1 to Continue.
:}

 

[RiverGuy]

OK, the date syntax made me think maybe you were abstracting the query to the tool. I'm not sure why you need the format typed out exactly like you have unless you're passing it in to some application which takes it in a certain way.

I mean as long as it's evaluating to the correct date value, then you're fine. What front end tool is writing this query?

 

[Woolers]

The front end tool is a bespoke helpdesk system that I think is based around Crystal Reports but not too sure - but it does rely on the date format being exactly as above, it cannot take the date format in any other way unfortunately....


Keyboard Not Detected.....
Press F1 to Continue.
:}

 

[Woolers]

Anyone ??

Keyboard Not Detected.....
Press F1 to Continue.
:}