Post reply

How do I get this date format?

  • February 28, 2011 at 12:03 pm

    #246303

    1/02/2011 1:41:00 a.m.

    d/MM/yyyy h:mm:ss a.m.

    From MSSQL table Datetime field to VARCHAR(100)

    any ideas??

  • February 28, 2011 at 12:05 pm

    #1292506

    Take a look at the DATEPART function. That's probably your best bet.

  • February 28, 2011 at 12:07 pm

    #1292510

    have a look at what kind of fromat options convert brings.

    Check Books Online. "CAST and CONVERT (Transact-SQL) "

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 28, 2011 at 12:07 pm

    #1292511

    as it is clear for the format shown , Why you want only one digit for day and hour, what you will do for the dates more than 9?

    :w00t:

  • February 28, 2011 at 12:22 pm

    #1292521

    This is the format that a report I have exports to excel. I am just try to replicate it.

    How do I get .AM or .PM ??

    Do i have to write some fancy UDF??

  • February 28, 2011 at 12:25 pm

    #1292522

    here's a quick example of a bunch of the formats available. sometimes you need to combine some of them togetehr to get exactly what you are after:

    FormattedDate Code SQL

    20110228-14:22:56:983 --- SELECT CONVERT(VARCHAR(35),getdate(),112) + '-' + CONVERT(VARCHAR(35),getdate(),114)

    20110228-142256 --- SELECT CONVERT(VARCHAR(35),getdate(),112) + '-' + REPLACE(CONVERT(VARCHAR(35),getdate(),108),':','')

    20110228-142256983 --- SELECT CONVERT(VARCHAR(35),getdate(),112) + '-' + REPLACE(CONVERT(VARCHAR(35),getdate(),114),':','')

    02/28/2011 101 SELECT CONVERT(VARCHAR(35),getdate(),101)

    2011.02.28 102 SELECT CONVERT(VARCHAR(35),getdate(),102)

    28/02/2011 103 SELECT CONVERT(VARCHAR(35),getdate(),103)

    28.02.2011 104 SELECT CONVERT(VARCHAR(35),getdate(),104)

    28-02-2011 105 SELECT CONVERT(VARCHAR(35),getdate(),105)

    28 Feb 2011 106 SELECT CONVERT(VARCHAR(35),getdate(),106)

    Feb 28, 2011 107 SELECT CONVERT(VARCHAR(35),getdate(),107)

    14:22:56 108 SELECT CONVERT(VARCHAR(35),getdate(),108)

    Feb 28 2011 2:22:56:983PM 109 SELECT CONVERT(VARCHAR(35),getdate(),109)

    02-28-2011 110 SELECT CONVERT(VARCHAR(35),getdate(),110)

    2011/02/28 111 SELECT CONVERT(VARCHAR(35),getdate(),111)

    20110228 112 SELECT CONVERT(VARCHAR(35),getdate(),112)

    28 Feb 2011 14:22:56:983 113 SELECT CONVERT(VARCHAR(35),getdate(),113)

    14:22:56:983 114 SELECT CONVERT(VARCHAR(35),getdate(),114)

    2011-02-28 14:22:56 120 SELECT CONVERT(VARCHAR(35),getdate(),120)

    2011-02-28 14:22:56.983 121 SELECT CONVERT(VARCHAR(35),getdate(),121)

    2011-02-28T14:22:56.983 126 SELECT CONVERT(VARCHAR(35),getdate(),126)

    25 ???? ????? 1432 2:22:56:983PM 130 SELECT CONVERT(VARCHAR(35),getdate(),130)

    25/03/1432 2:22:56:983PM 131 SELECT CONVERT(VARCHAR(35),getdate(),131)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 28, 2011 at 2:35 pm

    #1292597

    What I am after is a date that has day, month, year, hour, minutes and secound in it that goes from an tSQL export to a "," csv file, that opens up in excel and can be cut and pasted back into MS access as a string , that can be inserted back into a MSSQL datetime field .

    But Excel screws with the date when you open the CSV file??

    So whats the best datetime STRING Format for a a csv file, that wont be lost in the opening of the csv file in excel????

  • February 28, 2011 at 2:56 pm

    #1292613

    i made a simple csv with a bunch of the possible dateformats.

    it looks like using convert with format 109 or 126 is probably best...it leaves the date and time alone as text:

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 28, 2011 at 3:11 pm

    #1292618

    Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply