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

convert varchar from mon dd yyyy hh:miAM to yyyy-mm-dd

hello all

converting from mon dd yyyy hh:miAM to yyyy-mm-dd

i have tried CONVERT(varchar, @fup_date,120)
which retrured yyyy-mm-dd plus the hh:mi:ss(24h).is there a specific style which return only the yyyy-mm-dd or do i have to manually truncate the "hh:mi:ss(24h)" portion of it?

thanks in advance

0
spankenstien
Asked:
spankenstien
  • 4
1 Solution
 
pique_techCommented:
You can use CONVERT(DateTime,FLOOR(CONVERT(Int,GetDate()))) to return a value for the given date at midnight, which is really how SQL views/stores a date without an explicit time.  If you're asking how to just display that datevalue only, then you have to use string manipulation.
0
 
pique_techCommented:
Sorry, replace GetDate() with the date/time value you want to change.
              CONVERT(DateTime,FLOOR(CONVERT(Int,@MyDateTimeVariable)))
0
 
MartinCMSCommented:
try this...

select convert(varchar(10),  @fup_date,120)
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.

 
Snarf0001Commented:
Interesting pique_tech, I've always used the technique written by MartinCMS, but I like the integer conversion.

A note though, you'd need to convert it to a float, not an int.  If it's an integer, it will have already rounded off the decimal portion itself before ever reaching you're FLOOR command, so anything after noon would come up as midnight of the next day.
0
 
pique_techCommented:
Thanks, you're right:  I actually made that same mistake myself a long time ago (thus I forgot and did it again).  
0
 
pique_techCommented:
So, the final, correct version of my suggestion is, using spankenstien's variable name:
             CONVERT(DateTime,FLOOR(CONVERT(Float,@fup_date)))
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: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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