Click here to Skip to main content
15,559,200 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i have sql query below

SELECT convert(nvarchar, dtBirthDate, 103) as 'Date Of Birth'
FROM tblEmployee

it give result "Dec 29 2015 12:00AM"
Posted
Updated 9-Jan-16 0:51am
v2

The SQL CONVERT function converts between formats - and the DATETIME specifier 103 does indeed generate "mm/dd/yyyy".
Unfortunately, it only works when you feed it a DATETIME value to convert to a VARCHAR or NVARCHAR value - so if you are getting "Dec 29 2015 12:00AM" from your SELECT, then that's because you are storing your dates in VARCHAR or NVARCHAR fields already, and SQL won't convert from a string based field to a string based field.
Try it yourself:
SQL
SELECT convert(nvarchar, GETDATE(), 103) as 'Date Of Birth1',
       convert(nvarchar, GETDATE())as 'Date of Birth2',
       convert(nvarchar, convert(nvarchar, GETDATE()), 103) as 'Date Of Birth3'

Always store values as the appropriate datatype: anything else will give you problems later.
Change your database design now, before it does become a problem to fix - or at some point an invalid date value will get in there, and it will get to be a real problem to sort out!
 
Share this answer
 
v2
Comments
Amit Bhoi 9-Jan-16 6:38am    
thank you OriginalGriff for explaining
OriginalGriff 9-Jan-16 6:56am    
You're welcome!
Maciej Los 9-Jan-16 6:59am    
5ed!
Wendelius 9-Jan-16 8:31am    
Well explained, deserves a 5
Format 103 yields to the desired output so most likely if the value isn't shown correctly the problem lies elsewhere.

Have a try with
SQL
SELECT convert(nvarchar, GETDATE(), 103) as 'Date Of Birth'

the result is
Date Of Birth
09/01/2016

However, the length for the nvarchar should be defined since without specifying the length it would be 1. In other words you should prefer
SQL
SELECT convert(nvarchar(10), GETDATE(), 103) as 'Date Of Birth'
 
Share this answer
 
Comments
OriginalGriff 9-Jan-16 6:55am    
Down vote countered.
Wendelius 9-Jan-16 8:30am    
Thanks :)

Don't worry about those, they come and go :)
OriginalGriff 9-Jan-16 8:42am    
:laugh: I don't worry about 'em!
But gawd, some people do...you'd think they were worth money or something. ;)
Wendelius 9-Jan-16 9:20am    
They aren't worth a pound each? What a pity :)
OriginalGriff 9-Jan-16 10:01am    
I'd be happy if they were worth a Canadian dollar each!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900