• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

ms sql 2008- split a date

Hi,
I need to split a date at " " to remove the time at the end and only display the date. StartDate and EndDate need to have the end trimmed off.
Can someone show me how to do this please?
Thanks
CREATE PROCEDURE lpw_GetEventsForCounty
 
@County NVarChar (50)
 
AS
 
SELECT RegisterID, UserID, EventName, VenueName, StartDate, EndDate, StartTime, EndTime , EntranceFee, Town, County, EventDetails, EventContact, EstimatedAttendees FROM lpw_tblRegisterEvent  WHERE County = @County
GO

Open in new window

0
CharlieDev
Asked:
CharlieDev
  • 4
  • 2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
CREATE PROCEDURE lpw_GetEventsForCounty
 
@County NVarChar (50)
 
AS
 
SELECT RegisterID, UserID, EventName, VenueName
, CONVERT(varchar(10), StartDate, 120) start_date
, CONVERT(varchar(10), EndDate, 120), end_date
, StartTime, EndTime 
, EntranceFee, Town, County, EventDetails, EventContact, EstimatedAttendees 
FROM lpw_tblRegisterEvent  
WHERE County = @County
GO

Open in new window

0
 
BrandonGalderisiCommented:
If you are using SQL 2008, as your TAGS suggest, you can cast the value as DATE or TIME data types.

select ........, cast(startdate as date), cast(starttime as time), ....
from ....
CREATE PROCEDURE lpw_GetEventsForCounty
 
@County NVarChar (50)
 
AS
 
SELECT RegisterID, UserID, EventName, VenueName, 
cast(StartDate as date) as startdate
, cast(EndDate as date) as enddate
, cast(startdate as time) as StartTime
, cast(enddate as time) as EndTime 
, EntranceFee, Town, County, EventDetails, EventContact, EstimatedAttendees FROM lpw_tblRegisterEvent  WHERE County = @County
GO

Open in new window

0
 
CharlieDevAuthor Commented:
Thanks, I have a few problems with it though

I have changed it to :
SELECT RegisterID, UserID, EventName, VenueName
, CONVERT(varchar(10), StartDate, 120) StartDate
, CONVERT(varchar(10), EndDate, 120),EndDate

as the start_date was throwing an error

Now I have the time trimmed off the StartDate but not the EndDate, and the startdate is being shown in the wrong format, i did have 14/02/2009 and now its  2009/02/14

Any idea what I've got wrong?
Thanks
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
CharlieDevAuthor Commented:
the above comment was for angelll
thanks :)
0
 
CharlieDevAuthor Commented:
I'm not using sql 2008! ops. I normally do but I've had to go back to ms 2000 for this client

So I have an error that the date type is not a defined system type, guessing because they hadnt put it in back in 2000, bummer!
Thanks though and sorry for the mis information
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>did have 14/02/2009 and now its  2009/02/14
use 103 instead of 120 in my suggestion
0
 
CharlieDevAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now