VISUAL STUDIO VB - Trying to format a date in SQL INSERT Statement - Time 00:00:000 Still showing

Trying to format a date in SQL INSERT Statement - Time 00:00:000 Still showing

CONVERT(varchar(10), GETDATE(),101)) still displays blank time

If I add an AS 'MM/DD/YYYY'
SQL gives me an error on the AS
ralphp1355Asked:
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.

silemoneCommented:
Do you have your entire SQL code?
0
ralphp1355Author Commented:
Its a Insert into statement for an audit trail - actiondate is a datetime field ??Is that wrong?

Insert into datatable
(username, actiondate)
values
("user",CONVERT(varchar(10), GETDATE(),101)) )
0
silemoneCommented:
you could honestly just store date as string and convert whenever you need to do range with it if that's the case.  But I'm running code on  my end now.
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
If the actiondate column is a datetime field then SQL Server will add the time '00:00:000' everytime you make an insert or update. If you only want the date then the field must be a date data type.
0
Shaun KlineLead Software EngineerCommented:
If you mean that "00:00:00" is showing when you select data after you run the insert, that is correct. A datetime field will default the time to 12am when no time component is provided when entering a value into a datetime field.
0
silemoneCommented:
btw DataTime is different from what GETDATE offers

DateTime and Date are different types in MSSQL
0
silemoneCommented:
Ahh Shaun Kline is correct...you did not say, but we have to assume you are not passing time value as to you are only passing varchar (10)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Potentially stupid question>  What's wrong with CAST(GETDATE() as date) , or if this has to be passed as a character to concatenate with something else SELECT(CONVERT(varchar(10), CAST(GETDATE()) as date)) ?
0
silemoneCommented:
Cast and Convert do similar things but...the select (convert and cast) statement will be heavier thought with such a small statement it will not matter, however, it will result in him learning inefficient coding...
0
ralphp1355Author Commented:
Thanks Jim - Everyone - that still didn't work. I must have tried 20 different things and still get the blank date

INSERT INTO datatable
           ([UserName],[ActionDate])
     VALUES
           ('uname',CONVERT(VARCHAR(10),CAST(GETDATE()as DATE),101))
 
 
2015-05-29 00:00:00.000
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Which version of SQL Server are you running?  I see 2005 in the zones, but nowhere else in the question.  If so the date data type was first introduced in SQL 2008 (click on 'Other Versions'), so it would not be available for 2005 T-SQL coding and as a column data type.

And I don't have 2005 on my dev box, so can't help you there..
0
Shaun KlineLead Software EngineerCommented:
If you are truly doing this for an audit trail, you should probably just be using GETDATE() without converting or casting it. This would give the both the current date and time.
0
silemoneCommented:
ok so you do not want any times to show at all, correct?
0
silemoneCommented:
what type of field is your storage in the DB?  A datetime?
0
ralphp1355Author Commented:
Yes datetime
0
silemoneCommented:
well if you don't want time it should be a Date or if you don't want to go that route, just remove time portion if you keep it as date time with your server side code.  I tested your insert and it works as it was originally written.
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
ralphp1355Author Commented:
I exported it to excel it looks fine thank you everyone
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.