Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1058
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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