Solved

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

Posted on 2004-09-17
6
1,030 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 20 total points
ID: 12086116
try this...

select convert(varchar(10),  @fup_date,120)
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Removing SCCM 2016 4 53
databases from multiple applications - same instance 6 40
SQL Percentage Formula 7 33
rolling count by date, hour query 7 32
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

710 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