Query Issue 2

[TheConeHead]

Is there a way to take "23 Nov 2006" and use it to query a datetime field (i.e. 11/23/2006 7:45:12 PM)

 

[gmmastros]

Select *
From   Table
Where  DateAdd(Day, DateDiff(Day, 0, DateField), 0) = '23 Nov 2006'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nmalhot]

you should be able to use WHERE dateField > '10-Nov-2006' and it will return any rows where datefield is after 10-Nov-2006 00:00:00

 

[gmmastros]

From a performance perspective, this should run faster and also return the same data.

Declare @Temp DateTime
Set @Temp = '23 Nov 2006'

Select *
From   Table
Where  DateField >= @Temp
       And DateField < @Temp + 1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

DECLARE @Test varchar(50)
DECLARE @Test1 varchar(50)
DECLARE @Test2 varchar(3)
SET @Test = 'JanFebMarAprMayJunJulAugSepOctNovDec'
SET @Test1 = '23 Nov 2006'
SET @Test2 = SUBSTRING(@Test1,4,3)
print convert(datetime, RIGHT(@Test1,4)+RIGHT('00'+CAST(CharIndex(@Test2,@Test)/3+1 as varchar(2)),2)+LEFT(@Test1,2))

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

George,
this raise error on my computer:

DECLARE @Test datetime
SET @Test = '23 Nov 2006'

Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Boris. It's a regional setting thing...

[!]Set language 'us_english'[/!]
DECLARE @Test datetime
SET @Test = '23 Nov 2006'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

If you run...

[!]sp_helplanguage[/!]

You will see the acceptable month abbreviations for various languages.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

I know, just maybe my sense of humor today is rough , or it is absent at all .
I just wanted to point that this conversion is language dependent. Sorry.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP