locked
How do I use the Sql standard 2008 data type datetime in a format such as 22-Sep-10? RRS feed

  • Question

  • Hi,

     I am new to database's and have had very little experience with them. I have used local compact 3.5 some but not the sql standard 2008 that I am using in this application.

     I have a problem with Sql standard 2008. I have a customer who has several workstations setup with these databases. Every one of the databases uses an identical table and data types. I am writing an application for them and have to use a database exactly like theirs is setup. I have set up the database with the same data types they are using.

     Here is the problem I have several columns setup as datetime data type and his server database it shows a date in the datetime field in this format 22-Sep-10. I have try several methods of sending different formated dates but have not been succesful in getting the field to show the correct format it always shows 09/22/2012 12:00:00.
     He copied a row of data and sent me. This row had the datetime format as 22-Sep-10.

    I know there must be a way to do this. Some of the post I have read indicated the format could be set other than changing the data type. It would be fairly simple to format the date and use a char data type but I cannot change the data type for the DB due to the requirements set by the IT manager.

    Any suggestion or examples would be most appriciated.

    Curtis


    Always Lost in Code, Always mark answers as correct if they answer you question and solve your problem. This way others when searching for similar problems can find the answer faster.
    Thursday, September 23, 2010 6:17 AM

Answers

  • This is display format, not the storage format.

    If you want SQL to provide it to you, you need to do the conversion to varchar. Unfortunately SQL does not have predefined format style like you need, so you have to use some string functions. Something like below:

    declare
    	@Date datetime = '2010-09-22'
    	
    select
    	@Date, Replace(Left(convert(varchar(7),@Date,13),7),' ','-') + CONVERT(varchar(2),Year(@Date) - 2000)
    	
    
    Again, I think it would make sense to use regular datetime and change display formatting in the client application. With varchar conversion approach you'll lose an ability to sort and effectively filter the data.

    As the side note, when you send date back to sql, use SqlParameter with SqlDbType.DateTime data type. If you need to use text representation - use: "yyyy-MM-ddThh:mm:ss' format

     


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by Curtis UN Thursday, September 23, 2010 2:36 PM
    Thursday, September 23, 2010 12:36 PM
  • In addition please read Tibor's artricle

    http://www.karaszi.com/SQLServer/info_datetime.asp


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Curtis UN Thursday, September 23, 2010 2:37 PM
    Thursday, September 23, 2010 1:45 PM

All replies

  • This is display format, not the storage format.

    If you want SQL to provide it to you, you need to do the conversion to varchar. Unfortunately SQL does not have predefined format style like you need, so you have to use some string functions. Something like below:

    declare
    	@Date datetime = '2010-09-22'
    	
    select
    	@Date, Replace(Left(convert(varchar(7),@Date,13),7),' ','-') + CONVERT(varchar(2),Year(@Date) - 2000)
    	
    
    Again, I think it would make sense to use regular datetime and change display formatting in the client application. With varchar conversion approach you'll lose an ability to sort and effectively filter the data.

    As the side note, when you send date back to sql, use SqlParameter with SqlDbType.DateTime data type. If you need to use text representation - use: "yyyy-MM-ddThh:mm:ss' format

     


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by Curtis UN Thursday, September 23, 2010 2:36 PM
    Thursday, September 23, 2010 12:36 PM
  • In addition please read Tibor's artricle

    http://www.karaszi.com/SQLServer/info_datetime.asp


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Curtis UN Thursday, September 23, 2010 2:37 PM
    Thursday, September 23, 2010 1:45 PM
  • Hi Dmtri and Uri,

     Thanks for the information. Now I uderstand what that no data type can not be manipulated in the database. What ever is displaying the data from the DB is controling how the data is displayed. I can not change the data type due to it has to match thier other databases. The Database is just a small part of this application I am wriiting. The rest of the application was fairly easy but since I have limited experience with databases this is cause some difficulty for me.

    Thanks for the tip about using SqlParameter with SqlDbType.DateTime data and the link.

     I had asked Ken a forum friend about this also in an email. I recieved his reply this morning. Before I got the alert, Which I don't always get for some reason.
                 "  The database is storing a datetime field.  You do not have control over what it looks like in the database.  Your application will have to format the datetime when you display it."

     I thank all of you for your answers. I saved the link for reference because I have a feeling I will need it.

     

     Curtis

     

     


    Always Lost in Code, Always mark answers as correct if they answer you question and solve your problem. This way others when searching for similar problems can find the answer faster.
    Thursday, September 23, 2010 2:35 PM