Solved

Getdate format

Posted on 2008-10-13
16
1,356 Views
Last Modified: 2012-08-13
Hi
Using enterprise manager I insert Getdate() into default column which returns todats date and time every time I inser a record. Is there some way I can change the format from dd,mm, yyyy,hh,mm to dd.mm,yyyy.
I have tried   SELECT     *, CONVERT(VARCHAR, GETDATE(), 3) AS Dateentered
FROM         EmployeeTime2  but this changes the dates in all rows.

Camnoc
0
Comment
Question by:Camnoc
  • 6
  • 5
  • 4
16 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22702394
Camnoc,

1) Do not confuse the way a date value is stored with how it is displayed.

2) You seem not to want the formatting applied to all rows.  What is it that you are after?

Patrick
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22702806
If you are looking to strip the datetime of the time, I would suggest this.


convert(CONVERT(VARCHAR(10), GETDATE(), 101),101)

BUT, as matthewspatrick has pointed out, don't confuse the storage with the display.  It will still store it in a datetime field and will be returned (with the time) based upon the regional settings of the server.

0
 

Author Comment

by:Camnoc
ID: 22703323
OK so my knowledge of sql is really bad, I am probably relying on Enterprise Manage too much, however heres my table.
SELECT     empid, Time_in, time_out, CONVERT(VARCHAR(10), GETDATE(), 101) AS Date_only, Date_entered  FROM         dbo.EmployeeTime2.      
Date_entered shows the date as records are inserted but in format dd,mm,yyyy, hh,mm.  Date_only will show date in the correct format but in all rows. I just want to insert the getdate() in dd,mm, yyyy every time a record is inserted??
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!

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22703507
Camnoc said:
>>I just want to insert the getdate() in dd,mm, yyyy every time a record is inserted??

If the column you are inserting into is defined as datetime, then the format *does not matter*.  Just let
it go :)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22703513
Or smalldatetime, for that matter...
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22704481
smalldatetime really only drops the seconds and milliseconds.  You still have the hours and minutes portion of the time.
0
 

Author Comment

by:Camnoc
ID: 22704593
Hi  BRANDON
Thank you so much, I think am going to give up on using SQL for my timesheet project Excel is a far simpler option. There may be a dreamweaver extension available I hav'nt looked.
Sorry I gave all the points to Matthewspatrick ,I clicked the wrong Button, I should have split the points.
I will be careful the next time.
Thanks for all your help.

Camnoc
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22704816
Camnoc,

Use the 'request attention' link to ask a Mod to reopen the question.

Regards,

Patrick
0
 

Author Comment

by:Camnoc
ID: 22704996
Where is the 'request attention' button . I really shouldnt get up on Mondays:)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22705800
It's in the main body of your question at the top of the page.
0
 

Author Comment

by:Camnoc
ID: 22709511
Hi Brandon

The question is reopened.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22711642
OK?  

You asked "Where is the 'request attention' button" and I responded "It's in the main body of your question at the top of the page.".  

Did you need help with something else because all you said was "I should have split the points".
0
 

Author Comment

by:Camnoc
ID: 22715966
Hi Brandon

I have been trying to get my time calculations to work with little success. I will submit a new question with code attached tomorrow if you can you might have a look.

Paul
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22715990
Do it as a related question and I'll see it for sure.
0
 

Author Closing Comment

by:Camnoc
ID: 31407102
Your right
Thanks  very much.
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
SQL Replication question 9 41
Webservices in T-SQL 3 30
TSQL - How to declare table name 26 28
T-SQL: New to using transactions 9 19
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now