SELECT CONVERT(VARCHAR(10), GETDATE(), 105)
This query return the date in the [DD-MM-YYYY] format as varchar. I need the same format in datetime datatype in sql server. Pls help me out
3 Answers
In SQL Server, a DATETIME datatype is stored as 2 4-byte integers so as such doesn't have a particular formatting like this.
If you want to return the date in a specific format, you need to CONVERT it to VARCHAR with the appropriate format identifier specified.
If you have a datetime in a VARCHAR and want to store that in a DATETIME field in SQL Server, then you should make sure you pass that value to SQL in a format that will always be safely interpreted. e.g. dd/mm/YYYY format is not safe as depending on settings, it could be treated as mm/dd/yyyy when it goes in. Safe formats are:
yyyyMMdd
yyyy-MM-ddThh:mi:ss.mmm
e.g.
INSERT MyTable (DateField) VALUES ('01/10/2010') -- dd/MM/yyyy not safe
INSERT MyTable (DateField) VALUES ('20101001') -- yyyyMMdd safe
Update:
When you SELECT a DATETIME field (GETDATE(), field, variable....) what you see in SSMS is a formatted value as this is what is useful to you, instead of it showing it's actual internal 8byte representation.
Comments
Here it is,
SELECT CONVERT(DATETIME, GETDATE(), 105)
Just Changed VARCHAR to DATETIME
2 Comments
Muhammad Akhtar
that is not what he is looking for.
Harsh Baid
@Robinclave check this anubhavg.wordpress.com/2009/06/11/… . It might help, but i think to format date is possible but getting it in
datetime datatype is only possible by changing the sql server settings (i.e might be system settings)This will return a datetime
SELECT GETDATE()
3 Comments
Robin clave
here, the return type is varchar right? I need it in datetime datatype
AdaTheDev
@Robin Clave - no, GETDATE() returns DATETIME
Robin clave
Thats true. I said for SELECT CONVERT(VARCHAR(10), GETDATE(), 105)
SELECT CAST(CONVERT(VARCHAR(10), GETDATE(), 105) AS DATETIME)