Update sting value to Date DataType field by using Trigger.
If you have any other questions, please post more details information about your requirement.
Best regards,
Sam
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
If reverse to be done . In Sql in Date field Data is stored as yyyy-mm-dd. In String field i want it as dd-mm-yyyy.
You have asked the same question several times now. As explained in your other threads. DateTime is a type, it is actually numeric. The date is NOT stored as yyyy-mm-dd unless you defined the column as a VARCHAR whihc is unclear at this point.
Reading the documentation and taking time to learn the basics really helps.
Seems some confusion here. A date column doesn't have any format. Most DBAs or developer tools are using their own format so that all DBAs or developers are seeing the same unambigious value accross the world.
Then for users you are showing the SAME values according to whatever best fit their own country convention. If doing that at the SQL level you'll then get a string column which can cause issues (for example if you want to do a calculation with that on the
C# side you would need to convert back that string to a date or it would be harder to create a multilingual site).
So this is done usually as late (ie when the value is about to be rendered by the UI) as possible using :
- the current culture
- an additional formatting indication such as not being interested in minutes/seconds...
And so the 2019-07-15 and 1.234 value you see in SSMS could be shown on your site as :
15/07/2019 and 1,234 for a French user
07/15/2019 and 1.234 for a US user
etc...
Member
110 Points
384 Posts
Update Date field
Jul 17, 2019 08:59 AM|jsshivalik|LINK
Hi
In Sql table i have string value as 15/07/2019. I want to update another field which has Date DataType with this value
Thanks
All-Star
45840 Points
16682 Posts
Re: Update Date field
Jul 17, 2019 10:39 AM|PatriceSc|LINK
Hi,
So it seems you need something such as :
UPDATE t SET b=CONVERT(DATE,a,103) -- Assuming SQL Server
See https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#date-and-time-styles
Participant
1860 Points
707 Posts
Re: Update Date field
Jul 17, 2019 10:40 AM|samwu|LINK
Hi jsshivalik,
I have answered similar questions to you on this thread：https://forums.asp.net/t/2157817.aspx
Update sting value to Date DataType field by using Trigger.
If you have any other questions, please post more details information about your requirement.
Best regards,
Sam
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
110 Points
384 Posts
Re: Update Date field
Jul 17, 2019 10:56 AM|jsshivalik|LINK
Hi Patrice
If reverse to be done . In Sql in Date field Data is stored as yyyy-mm-dd. In String field i want it as dd-mm-yyyy.
Thanks
All-Star
43731 Points
18710 Posts
Re: Update Date field
Jul 17, 2019 11:41 AM|mgebhard|LINK
You have asked the same question several times now. As explained in your other threads. DateTime is a type, it is actually numeric. The date is NOT stored as yyyy-mm-dd unless you defined the column as a VARCHAR whihc is unclear at this point.
Reading the documentation and taking time to learn the basics really helps.
All-Star
45840 Points
16682 Posts
Re: Update Date field
Jul 17, 2019 11:42 AM|PatriceSc|LINK
Seems some confusion here. A date column doesn't have any format. Most DBAs or developer tools are using their own format so that all DBAs or developers are seeing the same unambigious value accross the world.
Then for users you are showing the SAME values according to whatever best fit their own country convention. If doing that at the SQL level you'll then get a string column which can cause issues (for example if you want to do a calculation with that on the C# side you would need to convert back that string to a date or it would be harder to create a multilingual site).
So this is done usually as late (ie when the value is about to be rendered by the UI) as possible using :
- the current culture
- an additional formatting indication such as not being interested in minutes/seconds...
And so the 2019-07-15 and 1.234 value you see in SSMS could be shown on your site as :
15/07/2019 and 1,234 for a French user
07/15/2019 and 1.234 for a US user
etc...
Start with :
- use https://documentation.devexpress.com/AspNet/12062/Localization/How-to-Set-a-Culture-for-an-ASP-NET-Web-Page so that your web site uses the culture you want (more likely you support one culture only ?)
- then it depends on which UI approach your are using but you can use "Data Annotations" or a https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings#the-short-date-d-format-specifier so that the UI knows how to render that value (and you still keep the ability to add support for other languages as needed)