Converting datetime

Question

Converting datetime

krishusavalia

SSCrazy

Points: 2385
More actions
January 25, 2012 at 7:49 am

#251809

Hi Everyone,
SELECT GETDATE()
result: 2012-01-25 09:44:25.777
Expected result: 20120125094425.777
or If date is coming from the table is like : 2012-12-31
Expected result: 20121231000000.000
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

I can't imagine why in the world you want this "format". Of course formatting really belongs in the front end and not in sql but you can do it. You have to get creative with convert and replace to pull this off.

something like this should work for you.

declare @MyDate datetime = getdate()

set @MyDate = '12/31/2012'

select @MyDate, cast(replace(replace(replace(Convert(Varchar, @MyDate, 127), '-', ''), 'T', ''), ':', '') as numeric(20,3))

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Paul White SSC Guru Points: 150467 More actions · Answer 2

Whatever else, always store dates and times using one of the built-in types, never as a string.

As an aside, this sort of formatting is made easier and faster in SQL Server 2012:

DECLARE @d datetime = '2012-01-25 09:44:25.777';

SELECT FORMAT(@d, 'yyyyMMddhhmmss.fff');

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

HowardW One Orange Chip Points: 29109 More actions · Answer 3

Sean Lange (1/25/2012)
I can't imagine why in the world you want this "format". Of course formatting really belongs in the front end and not in sql but you can do it. You have to get creative with convert and replace to pull this off.

I do have some sympathy in terms of meeting particular datetime format requirements. I do a lot of work integrating between various 3rd party and internal systems, some of which are done through flat-files with specific format requirement documents. You're always ultimately dealing with strings in a flat file and I've seen all sorts of formats that are standards in a particular industry or technical platform, many of which aren't covered by the scattershot of formats that are pre-defined in SQL Server.

In this sort of scenario, there is no front-end. Mostly it's going through SSIS eventually, but it's neater to do the manipulation in a SQL view that meets the spec first, then you can use SSIS in a more reuseable fashion to pump out the data to whatever destination is required.

I'm really glad they've added the FORMAT function in 2012. As much as there's an assumption that date format manipulation shouldn't happen in the database, it's nonetheless important for a lot of use cases.

Paul White SSC Guru Points: 150467 More actions · Answer 4

HowardW (1/27/2012)
I'm really glad they've added the FORMAT function in 2012. As much as there's an assumption that date format manipulation shouldn't happen in the database, it's nonetheless important for a lot of use cases.

Yes, me too. It exposes many of the powerful features in the .NET framework (yes FORMAT uses SQLCLR) and is much faster than manipulating strings in T-SQL. Same goes for other new language features like DATEFROMPARTS.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

I'm really glad they've added the FORMAT function in 2012. As much as there's an assumption that date format manipulation shouldn't happen in the database, it's nonetheless important for a lot of use cases.

Me too. I know there are plenty of cases like you described and pulling in a certain, albeit sometimes a bit strange, format is just the way it has to be. Things like FORMAT will make this stuff a ton easier. It seems that more and more MS is adding things that are typically considered to be outside of sql (formatting, running totals, etc). Definitely making things easier.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

They're taking all of the fun out of it! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)