Solved

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

Posted on 2004-09-17
6
1,022 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 20 total points
ID: 12086116
try this...

select convert(varchar(10),  @fup_date,120)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Run SQL Server Proc from Access 11 31
VB.NET 2008 - SQL Timeout 9 24
SQL Query Help Top 1 and Distinct? 6 26
SQL2016 to ORACLE11G linked-server 6 15
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 …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

810 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