November 3, 2005 at 1:54 am
Why?
Everything is before your eyes.
Just don't be lazy.
SELECT ...
FROM TABLE
WHERE DateTimeColumn between convert(datetime, convert(int, GETDATE() - 0.5)) + convert(datetime, '1900-01-01 16:00:00') and convert(datetime, convert(int, GETDATE() - 0.5)) + convert(datetime, '1900-01-01 20:00:00')
_____________
Code for TallyGenerator
November 3, 2005 at 3:03 am
ok my problems
Field one =DateStart
Field Tow=DateEnd
2. how can i use in date format this -'01/11/2005 20:00:00'
thnks
November 3, 2005 at 3:15 am
If the problem is 'how do I enter dates', then you shouldn't use '01/11/2005 20:00:00' at all.
This format only leads to confusion and problems (is it november 1st or january 11th? - I can't tell, and neither can the database)
When you enter dates, always use yyyymmdd format - eg '20051101 20:00:00'
Which then would be something like:
SELECT ... FROM ... WHERE startDate >= '20051101 16:00:00' AND endDate <= '20051101 20:00:00'
This is assuming that 'startDate' and 'endDate' columns are datetime datatypes. If they are, the above works perfectly.
/Kenneth
November 3, 2005 at 3:22 am
Can you explain in plain English what are you trying to achive?
As I understand, you have table with log of calls or something and another table with tariff definitions, e.g. StartTime and EndTime.
And you need to select all calls started every day between StartTime and EndTime.
Is it right?
_____________
Code for TallyGenerator
November 3, 2005 at 4:24 am
ok
how to use always the the Date Today like this =(Getdate() +'20:00:00')
-------
SELECT ... FROM ... WHERE startDate >= (Getdate() +'16:00:00') AND endDate <=(Getdate() +'20:00:00')
THNKS
November 3, 2005 at 7:42 am
First you need to get rid of the time from GETDATE().
SELECT CONVERT(VARCHAR(8), GETDATE(), 112)
Then you need to add the new time to that:
SELECT (CONVERT(VARCHAR(8), GETDATE(), 112)) + ' ' + '16:00'
Then take all that and convert it back to datetime:
SELECT CONVERT(DATETIME, ((CONVERT(VARCHAR(8), GETDATE(), 112)) + ' ' + '16:00'))
Run each of those scripts to see what they do.
-SQLBill
November 3, 2005 at 8:22 am
can you fix this for me
---------
DECLARE @StartTime datetime, @EndTime datetime ,@Today smalldatetime,@Today2 smalldatetime
SET @Today = GETDATE()-2
SET @Today2 = GETDATE()-1
set @StartTime =@Today+' '+'16:00'
set @EndTime =@Today2+' '+' 20:00'
SELECT *
FROM dbo.MAINB
WHERE
tarih >= @StartTime AND tarihB >= @EndTime
AND (meosar LIKE '1')
November 3, 2005 at 8:33 am
November 3, 2005 at 8:59 am
no i try it
-----
DECLARE @a DATETIME, @b-2 DATETIME
SELECT @a = GETDATE()-2, @b-2 = GETDATE()-1
SELECT *
FROM dbo.MAINB
WHERE tarih >= CAST(SUBSTRING(CAST(@a AS BINARY(8)),1,4) + 0x107AC00 AS DATETIME)
AND tarihB <= CAST(SUBSTRING(CAST(@b AS BINARY(8)),1,4) + 0x1499700 AS DATETIME)
AND (meosar LIKE '1')
----------------
it work but i dont Catch all the dates !!!
###########################
i try this but also have problem Catch all the dates !!!
############################
SELECT TOP 100 PERCENT *
FROM dbo.notmdb
WHERE
(tarih >= CONVERT(DATETIME, ((CONVERT(VARCHAR(8), GETDATE()-2, 112)) + ' ' + '16:00')))
AND
(tarihb <= CONVERT(DATETIME, ((CONVERT(VARCHAR(8), GETDATE()-1, 112)) + ' ' + '20:00'))
##############################
HELP
i tray to Catch the date
tarih ='01/11/2005 16:00 '
tarihB ='02/11/2005 20:00 '
AND I DONT CATCH IT
WHAT IS THE PROBLEM
IT ONLY happen in sql server
when i run this SQl query in asp page from RECORDSET !
it working ok !!
i CATCH IT
when i use it in sql server i dont CATCH IT
any Explanation ????
ilan
November 3, 2005 at 9:57 am
DECLARE @StartTime datetime
, @EndTime datetime
SET @StartTime = CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 112) + ' 16:00:00')
SET @EndTime = CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 112) + ' 20:00:00') - 1
PRINT @StartTime
PRINT @EndTime
SELECT *
FROM dbo.MAINB
WHERE tarih >= @StartTime
AND tarihB >= @EndTime
AND (meosar LIKE '1')
November 3, 2005 at 12:30 pm
We are getting somewhere now...your problem is with the presentation of your date. SQL Server doesn't know what 01/11/2005 is. It could be January 11, 2005 or it could be 1 November 2005.
1. That is why dates should ALWAYS be entered as yyyymmdd.
2. If you can't enter dates in that format, you need to tell SQL Server how to interpret the dates. Use SET DATEFORMAT.
So, for your need do this:
SET DATEFORMAT DMY
SELECT TOP 100 PERCENT *
FROM dbo.notmdb
WHERE
(tarih >= CONVERT(DATETIME, ((CONVERT(VARCHAR(8), GETDATE()-2, 112)) + ' ' + '16:00')))
AND
(tarihb <= CONVERT(DATETIME, ((CONVERT(VARCHAR(8), GETDATE()-1, 112)) + ' ' + '20:00'))
See if that helps.
-SQLBill
November 3, 2005 at 12:35 pm
SELECT ...
FROM TABLE
WHERE
tarih >= convert(datetime, convert(int, GETDATE() - 0.5)) + convert(datetime, '1900-01-01 16:00:00')
and
tarihb <= convert(datetime, convert(int, GETDATE() - 0.5)) + convert(datetime, '1900-01-01 20:00:00')
_____________
Code for TallyGenerator
November 4, 2005 at 12:34 am
Use an independant and safe date format and make sure you read closely through this article:
November 4, 2005 at 6:01 am
Ilan,
Sorry, I reversed the calculations for StartTime and EndTime. The corrected version appears below. It's not clear as to what date range is required relative to GETDATE(). If today is Friday, Nov. 4, 2005, 8:00am EST (12:00 GMT), then is the desired range
START: Nov 3, 8:00 pm
END: Nov 4, 4:00 pm
as I have assumed, or is it
START: Nov 2, 8:00 pm
END: Nov 3, 4:00 pm
as SQLBill has assumed?
------------------------------------------------------------
DECLARE @StartTime datetime
, @EndTime datetime
SET @StartTime = CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 112) + ' 20:00:00') - 1
SET @EndTime = CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 112) + ' 16:00:00')
PRINT @StartTime
PRINT @EndTime
SELECT *
FROM dbo.MAINB
WHERE tarih >= @StartTime
AND tarihB >= @EndTime
AND (meosar LIKE '1')
November 4, 2005 at 7:56 am
mkeast,
Since it's GETDATE()-2 and GETDATE()-1, if it's Nov 4th, then:
Nov 4 - 2 = Nov 2
Nov 4 - 1 = Nov 3
-SQLBill
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply