[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2004-09-17
6
Medium Priority
?
1,072 Views
Last Modified: 2010-05-18
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
Comment
Question by:spankenstien
  • 4
6 Comments
 
LVL 12

Expert Comment

by:pique_tech
ID: 12085622
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12085636
Sorry, replace GetDate() with the date/time value you want to change.
              CONVERT(DateTime,FLOOR(CONVERT(Int,@MyDateTimeVariable)))
0
 
LVL 8

Accepted Solution

by:
MartinCMS earned 80 total points
ID: 12086116
try this...

select convert(varchar(10),  @fup_date,120)
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 23

Expert Comment

by:Snarf0001
ID: 12086447
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12086514
Thanks, you're right:  I actually made that same mistake myself a long time ago (thus I forgot and did it again).  
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12086542
So, the final, correct version of my suggestion is, using spankenstien's variable name:
             CONVERT(DateTime,FLOOR(CONVERT(Float,@fup_date)))
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question