Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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