passing string as datetime - 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

General SQL Server Forums

New to SQL Server Programming

passing string as datetime

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-09-26 : 14:43:14

I have a storedprocedure that returns the following:

dbo.spCount @StartDate= 'Sep 24 2011 12:00AM', @EndDate= 'Sep 24 2011 11:59PM', @ID=9

When I execute this value that gets returned I get NULL because I'm passing start and end date as string.

when I change my stored procedure to return this, it will generate this error: "Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Sep 24 2011 12:00AM'."

dbo.Count @StartDate= cast('Sep 24 2011 12:00AM' as datetime), @EndDate= cast('Sep 24 2011 11:59PM' as datetime), @ID=9

How could I pass it as a datetime instead of a string?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-26 : 14:45:41

What are the data types of those input parameters?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-09-26 : 14:47:00

HI Tkiz, they are datetime for startdate and enddate thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 14:49:54

pass the date values in universal format YYYYMMDD ie 20110924 etc
also for getting all records for a day use logic

datefield >=@startdate
and datefield< @enddate+1

after stripping off timepart

for stripping off timepart use

DATEADD(dd,DATEDIFF(dd,0,startdate),0) etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-09-26 : 14:51:51

Thanks for the reply. However I dont get what you are saying. It would be great if you could provide an example.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 14:55:00

pass like
dbo.spCount @StartDate= '20110924', @EndDate= '20110925', @ID=9

and in sp use like

datefield >=@startdate
and datefield < @enddate

for filter based on date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-09-26 : 14:57:34

It looks like the time part is important.
So you can use:
dbo.spCount @StartDate= '2011-09-24T12:00:00', @EndDate= '2011-09-24T23:59:00', @ID=9

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 15:00:59

i think OP is trying to return whole records that came on a day. even if enddate is passed as 2011-09-24T23:59:00 there's a chance it will lose some records in case its data populated after 2011-09-24T23:59:00 and before 2011-09-25T00:00:00 though probability is less. thats why i suggested better to pass next day start as end date and use >= and < to get everything between

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-09-26 : 15:38:07

Thanks V, however I need the result to return @StartDate='Sep 24 2011 12:00AM', @EndDate='Sep 24 2011 11:59PM' because the front end app needs it in that format. Is there any other way to do it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-26 : 15:49:22

What do you mean by "return"? @StartDate and @EndDate are input parameters, so they don't return anything. Right?

Can you show us the code for spCount as that'll help us understand what's going on?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-09-26 : 18:21:49

SOMEHOW WHEN I RUN THE SP WON'T WORK WITH 'Sep 24 2011 11:59PM' AS PARAMETER

DROP TABLE #TABLES
create table #table (orders int, dates datetime)
insert #table
select 1, '2011-09-24 23:59:59.000' union all
select 1, '2011-09-24 00:00:00.000'

alter proc sp
@dates datetime as

select * from #table where dates = @dates
select * from #table where dates = @dates
select CAST(dates as varchar(50)) DATES from #table

--run this first. GETS ONLY 2 ROWS WITH VALUES. WHY????? WHY IS NOT TAKING '2011-09-24 23:59:59.000'
exec sp @dates = 'Sep 24 2011 11:59PM'
--run this second. gETS 4 ROWS WITH VALUES
exec sp @dates = 'Sep 24 2011 12:00AM'

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-09-26 : 18:57:18

Never mind i solved it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-26 : 19:44:01

Glad you got it resolved. If you don't mind, could you please share what was done to fix it in case this issue arises for another poster in the future? It is helpful to provide the resolution in case your topic comes up in a search.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gongxia649
So Suave

344 Posts

Posted - 2011-09-26 : 23:14:17

I wrote a select statement to update 11:59PM to 11:59:59PM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources