calvo (8/6/2012)
what is the collation and data type of the field storing the current date?
Collation: SQL_Latin1_General_CP1_CI_AS
DataType: DateTime
GSquared (8/6/2012)
Dates in SQL Server aren't stored in any of those formats. They're stored as a number of days since "day 0" (1/1/1900 by default). The formatting you are seeing is done by the application layer, whether that's SSMS or something else.For example, today (6 August 2012), on my dev copy of SQL 2008 R2, is stored as 41125, since that's the number of days since 1 Jan 1900.
Try this:
SELECT CAST(GETDATE() AS INT),DATEDIFF(DAY, 0, GETDATE());
If you want the display to be different, change the settings in your application, or use a Convert mask for it.
I can use a Conversion Function to convert the Dat Format to the way I want but the resultant Date that I would have is a Varchar String and not a Date. If I try to conver/Cast it back to date it changes back to the same Date Format.
The problem above is still a problem that I would love to get some help on.
But, the Date and Time Format didn't matter in what I was trying to do. I have done it with using the same Format. But, I would still like to have some opinions from the Gurus if I can change the format of the Date without changing the Data Type.
Thank you very much for the replies.