DateTime data type - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DateTime data type

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2004-11-12 : 12:25:38

Hello all, this might be a basic question... but, I want to find some recrods by Date.. then i'm using CR to show them. In my stored procedure when I manually put '01/01/2004' they pull up fine.

BUT, when I just use my parameter in place - then use CR, and supply the date again, '01/01/2004' and I just leave the time to whatever it is - I get NO records.

I believe this is because of the TIME addition to DateTime? Because I see in a view is does this "CONVERT(DATETIME, '2004-01-01 00:00:00', 102)" .... So, is there a way to get rid of the TIME part? Or, what can I do in this situation?

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-12 : 12:48:36

quote:
Originally posted by hueby

I believe this is because of the TIME addition to DateTime? Because I see in a view is does this
"CONVERT(DATETIME, '2004-01-01 00:00:00', 102)" .... So, is there a way to get rid of the TIME part?
Or, what can I do in this situation?

the TIME part shouldn't be an issue here because '2004-01-01 00:00:00'='1/1/2004'

do you have set nocount on in your SP? if not add it at the begining of the SP

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

Auric
Yak Posting Veteran

70 Posts

Posted - 2004-11-12 : 13:31:26

what about tryin to search for
01/01/2004* ?

hueby
Posting Yak Master

127 Posts

Posted - 2004-11-12 : 13:55:18

Well to clarify, I am using a parameter in my SP "Datewrk = @date" in my WHERE clause. It will not let me add a * after it. AND, the only input going into @date is a mm/dd/yyyy format with NO time. Soo, I assume it adds on a 00:00:00 time after that - so when the SP runs it doesnt find any matches because of that time.??? I guess I need to have it only search/match by the DATE only. However I do that with the paramter?

Setting "set nocount on" didn't seem to anything, unless I did it wrong.

dsdeming

479 Posts

Posted - 2004-11-12 : 14:37:10

SET NOCOUNT ON has no effect on dates. It merely suppresses the output of the number of rows affected by a query. How about something like this:

WHERE CONVERT(DATETIME, ColumnName, 102) = CONVERT(DATETIME, @DateParam, 102)

It's brute force, but it should give you what you're after.

Dennis

hueby
Posting Yak Master

127 Posts

Posted - 2004-11-12 : 14:50:39

Thank you for the reply............. something is weird here. It still isn't working, BUT when I go into my SP and manually set a date - "mm/dd/yyyy" is works just fine. As soon as I set that parameter up .. nothing.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-12 : 15:41:12

well I had once a similar situation using a SP for a CR and I ran the SP on the
QA and i notice that before my expected results there was an
(1 rows affected) so I assumed that the SP was returning multiple recordsets and
I added the set nocount and got rid of the (1 rows affected) message and when I tested
on the CR it worked.

maybe is not the nocount but I really doubt that the problem is the date because all dates have time

you could try something like myDate >= @pDate and myDate < @pDate + 1

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-12 : 15:43:35

quote:
Originally posted by hueby

Thank you for the reply............. something is weird here. It still isn't working, BUT when I go into my SP and manually set a date - "mm/dd/yyyy" is works just fine. As soon as I set that parameter up .. nothing.

how are you setting the parameter???

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

hueby
Posting Yak Master

127 Posts

Posted - 2004-11-12 : 15:46:01

Like this - I assume this is what you are asking?... (@startdate DATETIME)

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-12 : 15:53:40

quote:
Originally posted by hueby

Like this - I assume this is what you are asking?... (@startdate DATETIME)

can it be a problem with regional settings? try ussing YYYYMMDD format instead

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

hueby
Posting Yak Master

127 Posts

Posted - 2004-11-12 : 15:56:43

Well, i have many other SP using the same thing, and they work fine... I do not think its my regional settings. :\

hueby
Posting Yak Master

127 Posts

Posted - 2004-11-12 : 16:04:38

.... Though, if it makes a difference, they are all "date1 BETWEEN @firstdate AND @seconddate" though, not just single date find "date1 = @date" ...... I would assume that wouldnt matter though.?

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-12 : 16:14:33

no more ideas

unless you can post your SP

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

hueby
Posting Yak Master

127 Posts

Posted - 2004-11-12 : 16:44:26

Thank you for your help. Can anyone else provide any hints to what I may be doing wrong? Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-12 : 18:12:26

Post your stored procedure. And also post what you are passing to it. Have you tried executing the stored procedure in Query Analyzer to see if it works there?

Tara

Kristen
Test

22859 Posts

Posted - 2004-11-13 : 01:27:33

I would put a PRINT statement in the SProc to display the parameters being passed so that you can see that they are exactly as you expect them to be. (And then run the SProc from Query Analsyer in Text mode, so you can see the output clearly)

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-13 : 10:02:18

Create a trace table and log the parameters to it. This will show you that you are passoing what you expect.
In this case you also might want to log the number of recs returned.
Another way is to use the profiler to show calls to the sp.
Then at least you will be sure about what you are passing to the sp and will know that it is the returning of the resultset that is the problem.

I take it you have checked that you are calling the correct sp and have verified it in cr?

Something like
create table trace (id int identity, type varchar(10), d datetime, s varchar(3000))

create proc mysp
@date1 datetime ,
@s varchar(20) ,
@i int
as

set nocount on
insert trace (type, d, s)
select 'start', getdate() ,
'<proc=mysp>'
+ '<spid=' + convert(varchar(20),@@spid) + '>'
+ '<@date=' + coalesce(convert(varchar(23),@date, 121), 'null') + '>'
+ '<@s=' + coalesce(@s, 'null) + '>'
+ '<@i=' + coalesce(convert(varchar(20),@i), 'null') + '>'

and at the end
select 'end', getdate() ,
'<proc=mysp>'
+ '<spid=' + convert(varchar(20),@@spid) + '>'
+ '<@date=' + coalesce(convert(varchar(23),@date, 121), 'null') + '>'
+ '<@s=' + coalesce(@s, 'null) + '>'
+ '<@i=' + coalesce(convert(varchar(20),@i), 'null') + '>'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hueby
Posting Yak Master

127 Posts

Posted - 2004-11-15 : 11:14:00

Edit/update: Using brute force as suggested earlier "WHERE CONVERT(DATETIME, ColumnName, 102) = CONVERT(DATETIME, @DateParam, 102)" will work. It pulls up fine now with that method. Whether or not it is the best way to do it, I dont know.

Thanks for the suggestions everyone. To answer your questions...

quote:
Post your stored procedure. And also post what you are passing to it. Have you tried executing the stored procedure in Query Analyzer to see if it works there?

Well in the Query Analyzer when I throw a date in there manually like '01/01/2001' it works fine. I dont know how to run it in the QA with actual parameters. Here is my Stored Procedure:

CREATE PROCEDURE [spStdLabor] (@startdate DATETIME)
 AS

SELECT     TOP 100 PERCENT dbo.PurchaseOrder.JobNo, dbo.PurchaseOrder.Descr, dbo.TBL_ACCTCODE.ACCTCODE, dbo.TBL_ACCTCODE.ABLDG, 
                      dbo.TBL_ACCTCODE.ADESC, dbo.TBL_ACCTCODE.AAREA, dbo.TBL_ACCTCODE.ATRACKID, dbo.PurchaseOrder.Purchord, dbo.Employee.fullname, 
                      dbo.Hours.Hourswrk, dbo.Hours.class, dbo.Hours.typewrk, dbo.Employee.EmployNo, dbo.Hours.Datewrk, dbo.Hours.formwrk, 
                      dbo.PurchaseOrder.compy, dbo.Hours.accttractingid
FROM         dbo.PurchaseOrder INNER JOIN
                      dbo.Hours ON dbo.PurchaseOrder.Purchord = dbo.Hours.Purchord INNER JOIN
                      dbo.Employee ON dbo.Hours.EmployNo = dbo.Employee.EmployNo INNER JOIN
                      dbo.TBL_ACCTCODE ON dbo.Hours.accttractingid = dbo.TBL_ACCTCODE.ATRACKID
WHERE   Hours.Datewrk = @startdate
GO

As for using the PRINT statement to see what parameters are being passed, can you provide a example on how to do this?

I am calling the correct SP through CR. Again, everything works perfect if I just use a manually coded date in my SP. As soon as I use the DATETIME parameter though, it does NOT.

In my VB.NET app it passes the parameter as mm/dd/yyyy - and the textbox is edited as a DATETIME.

The table was converted from Access. In the column I'm searching under the values are all mm/dd/yyyy. It is set to DATETIME, though after the date there is not time values.

-- I'll have to look into using that trace table code or using the profiler to show calls to the SP. These are methods I've never heard of yet. Thanks everyone...

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-15 : 13:15:46

quote:

quote:
...Have you tried executing the stored procedure in Query Analyzer to see if it works there?

Well in the Query Analyzer when I throw a date in there manually like '01/01/2001' it works fine. I dont know how to run it in the QA with actual parameters.

press F8, look for the SP on the explorer, right click it, and select debug
it will ask you for the parameter

quote:
As for using the PRINT statement to see what parameters are being passed, can you provide a example on how to do this?

try
Declare @startdate datetime
Set @startdate = '01/01/2001'

Print @startdate

SELECT TOP 100 PERCENT dbo.PurchaseOrder.JobNo, dbo.PurchaseOrder.Descr, dbo.TBL_ACCTCODE.ACCTCODE,
dbo.TBL_ACCTCODE.ABLDG, dbo.TBL_ACCTCODE.ADESC, dbo.TBL_ACCTCODE.AAREA, dbo.TBL_ACCTCODE.ATRACKID,
dbo.PurchaseOrder.Purchord, dbo.Employee.fullname, dbo.Hours.Hourswrk, dbo.Hours.class, dbo.Hours.typewrk,
dbo.Employee.EmployNo, dbo.Hours.Datewrk, dbo.Hours.formwrk, dbo.PurchaseOrder.compy, dbo.Hours.accttractingid
FROM dbo.PurchaseOrder INNER JOIN
dbo.Hours ON dbo.PurchaseOrder.Purchord = dbo.Hours.Purchord INNER JOIN
dbo.Employee ON dbo.Hours.EmployNo = dbo.Employee.EmployNo INNER JOIN
dbo.TBL_ACCTCODE ON dbo.Hours.accttractingid = dbo.TBL_ACCTCODE.ATRACKID
WHERE Hours.Datewrk = @startdate

quote:
after the date there is not time values.

When there is no explicit time you have 00:00:00:000

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

hueby
Posting Yak Master

127 Posts

Posted - 2004-11-19 : 11:43:54

Well, I thought it was working.. but I'm having trouble with it again now. Running it in the QA and debugging it brought this up: "[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification" ???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources