sql strip both the date and the time into separate fields from datetime field but keep values as date or time

I have a datetime field that I need to separate into a date field and into a time field.  I've found ways to convert it to a text field but I want it saved as a date or a time data type.  I tried a convert (date, fieldname) as newfieldname and I get a cannot call methods on date.  I thought this would be simple, but am finding it isn't.
cindyfillerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
E.g.

SELECT  CAST(DateTimeColumn AS DATE) ,
        CAST(DateTimeColumn AS TIME) ,
        DateTimeColumn
FROM    yourTable;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The above answer is correct.  As a follow-up, I'm guessing that you have a reason for wanting to do this, so it would be worth telling us, perhaps in a separate question, so we can give you some design advice / perspective.

I once had an airline for a client where I had to every date-time expression imaginable.
0
cindyfillerAuthor Commented:
I'm still getting the cannot call methods on date error.  I'm using sql 2008 and this is the specific line I'm using:
CAST(TimeBookingStart AS date) AS StartDate

We are integrating 2 software packages together - one does room reservations and the other is going to do the workflow for all of the reservations.  The vendor for the workflow wants the date and time separated out.  Since we will have to compare dates, I'd prefer to keep them as a date field so conversions aren't needed every time.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
Please post the exact error message. Where do you get this error message? What tool are you using? In short: more context, please..
0
cindyfillerAuthor Commented:
I'm in management studio working directly with the table.  Once its working I'll incorporate it into a view.  

When I try and run the select statement with the cast statement I get an error from management studio that says

SQL execution error
Executed SQL statement: select.... field listing
Error Source:  .net sqlclient data provider
Error Message:  Cannot call methods on date
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The error message doesn't look like a typical SSMS error. Eyeballing the above, let us know if you're coding in VB.NET or something else.   The only topics I see in this question are SQL Server, so if you're in VB.NET (or whatever) then it would be appropriate to add that topic.
0
Brian CroweDatabase AdministratorCommented:
what version of SQL Server are you running and what compatibility level?
0
cindyfillerAuthor Commented:
No - I'd be lost in vb.net!  I just create a new query and type in the code!
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just a thought ... it would help if you posted the query too, since this is a 'Why is my query returning an error' question.
0
cindyfillerAuthor Commented:
So this is weird.  I originally wasn't sure what table I needed so I was right clicking on the tables and taking the option to select top 1000.  That is where I was getting the error message.  I just copied this into a new query and ran it and it runs fine!!!  So I don't understand why it works in the query section and not the other, but it works and that is all that is important!!
0
ste5anSenior DeveloperCommented:
hmm, when I need to guess: You're using the Query Designer? Don't do this.
0
cindyfillerAuthor Commented:
Yea I was - trying to get a sense of what was in each table and then I just stayed there.  Lesson learned!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.