December 8, 2011 at 8:32 pm
In a sql server 2008 r2 database, I am trying to obtain just the date portion of a datetime field in mm/dd/yyyy format. I basically just do not want the time portion of this field.
I have used statements like select MONTH(pay_date) + '/' + DAY(pay_date) + '/' + YEAR(payment_date) from cust_table. I keep getting errors.
Can you tell me what is wrong with the above select statement and/or can you show me a different way to obtain only the date in a mm/dd/yyyy format?
December 8, 2011 at 9:54 pm
Will this do what you need ?
SELECT CAST(DATEPART(mm, GETDATE()) AS VARCHAR(2)) + '/' +
CAST(DATEPART(dd, GETDATE()) AS VARCHAR(2)) + '/' +
+ CAST(DATEPART(yy, GETDATE()) as VARCHAR(4))
Result:
12/8/2011
December 8, 2011 at 11:13 pm
Must you have the slashes or would this work?
SELECT CONVERT(DATE,GETDATE())
The output of that is a DATE format rather than a string and it is in format of yyyy-mm-dd
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 8, 2011 at 11:15 pm
Of course there is also the other way of doing it:
SELECT CONVERT(CHAR(10),GETDATE(),101)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 9, 2011 at 3:42 am
Formatting the date should not be done in T-SQL. Formatting (read: "creating an end-user representation of the date value") is the responsibility of the front-end. How would you implement showing the date as yyyy-mm-dd for one user and mm/dd/yyyy for another user, depending on their settings?
To strip the time component from a column with types datetime or datetime2 the easiest way is indeed to convert the value into a date type as was suggested:
select convert(date, yourcolumn)
Another method is to use dateadd() and datediff(), this used to be the best way to do it in SQL 2005, as this didn't have the "date"-type:
select dateadd(day, datediff(day, 0, yourcolumn), 0)
Both of these methods leave you with a locale independent date value that can be passed to a front-end application to be represented as needed for that applications purpose.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply