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.
cindyfillerDirector of ITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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

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 Data DudeCommented:
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.
cindyfillerDirector of ITAuthor 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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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..
cindyfillerDirector of ITAuthor 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
Jim HornMicrosoft SQL Server Data DudeCommented:
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.
Brian CroweDatabase AdministratorCommented:
what version of SQL Server are you running and what compatibility level?
cindyfillerDirector of ITAuthor Commented:
No - I'd be lost in vb.net!  I just create a new query and type in the code!
Jim HornMicrosoft SQL Server Data DudeCommented:
Just a thought ... it would help if you posted the query too, since this is a 'Why is my query returning an error' question.
cindyfillerDirector of ITAuthor 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!!
ste5anSenior DeveloperCommented:
hmm, when I need to guess: You're using the Query Designer? Don't do this.
cindyfillerDirector of ITAuthor Commented:
Yea I was - trying to get a sense of what was in each table and then I just stayed there.  Lesson learned!!
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.