April 22, 2015 at 5:15 am
Hi Experts,
I have created a table Employee with Hire_date with datatype as datetime.Then inserted a row with date converted to style 109[ convert(varchar(30),'01-Jan-2013 12:00:00:000AM',109)
After I have inserted ,while retrieving I cant view AM or PM.
Can anyone help me with the problem ...Please
Thanks,
Karthik
April 22, 2015 at 6:05 am
challakarthik (4/22/2015)
Hi Experts,I have created a table Employee with Hire_date with datatype as datetime.Then inserted a row with date converted to style 109[
convert(varchar(30),'01-Jan-2013 12:00:00:000AM',109)After I have inserted ,while retrieving I cant view AM or PM.
Can anyone help me with the problem ...Please
Thanks,
Karthik
The grid control in SSMS displays a datetime as a datetime. If you want to display it in another format, use CONVERT:
drop table #employee
create table #employee( EMPLOYEE_IDint, FIRST_NAMEvarchar(20), LAST_NAME varchar(20), SALARY INT, JOINING_DATE datetime, DEPARTMENT varchar(20))
---insert one row
INSERT INTO #employee (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT)
SELECT 1, 'John', 'Abraham', 1000000, CONVERT(DATETIME,'01-Jan-2013 12:00:00:000AM',109),'Banking'
--Here I cant find the AM or PM
SELECT *, CONVERT(VARCHAR(26), JOINING_DATE, 109)
FROM #employee
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 22, 2015 at 6:26 am
Thank you Chris
Is there any other datatype other than DATETIME ....So that i can get the date not in the military format.
Thanks,
Krthik
April 22, 2015 at 6:42 am
Datetimes should be stored in your tables as datetimes, and are displayed in the SSMS grid control in the format you are now familiar with. You can display them in any format you like. If CONVERT() doesn't have an output format which meets your requirements, you could tweak a suitable output to meet your needs.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 22, 2015 at 6:53 am
Thanks a lot Chris
April 22, 2015 at 7:08 am
I'll second the point: definitely store your dates and times in a column of type DATETIME. If you make that a non-negotiable starting point the rest of this will be easy.
Look into pushing formatting off to the application layer (if there is one) but if you have to provide formatted output direct from the database then FORMAT (added in SQL 2012) is a more intuitive way to output formatted dates in my opinion. One drawback of FORMAT is that it returns a VARCHAR(8000) by default so if you need type-safety you will need to cast the desired data-type:
DECLARE @dt DATETIME = '2015-04-22 13:00:00';
SELECT CAST(FORMAT(@dt, 'dd-MMM-yyyy h:mm:ss.ffftt') AS VARCHAR(50));
SELECT CONVERT(VARCHAR(50), @dt, 109);
Again, in my opinion, the formatting mask shown in the first select is simpler in terms of interpreting the intent at first glance of the code than is style 109 in the second select.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 22, 2015 at 10:02 am
Orlando Colamatteo (4/22/2015)
I'll second the point: definitely store your dates and times in a column of type DATETIME. If you make that a non-negotiable starting point the rest of this will be easy.Look into pushing formatting off to the application layer (if there is one) but if you have to provide formatted output direct from the database then FORMAT (added in SQL 2012) is a more intuitive way to output formatted dates in my opinion. One drawback of FORMAT is that it returns a VARCHAR(8000) by default so if you need type-safety you will need to cast the desired data-type:
DECLARE @dt DATETIME = '2015-04-22 13:00:00';
SELECT CAST(FORMAT(@dt, 'dd-MMM-yyyy h:mm:ss.ffftt') AS VARCHAR(50));
SELECT CONVERT(VARCHAR(50), @dt, 109);Again, in my opinion, the formatting mask shown in the first select is simpler in terms of interpreting the intent at first glance of the code than is style 109 in the second select.
The preferred format is: 'YYYYMMDD hh:mm:ss[.sss[ssss]]', because that is a universal format. Format yyyy-mm-dd will cause errors in regions where dates are normally yyyy-dd-mm.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 22, 2015 at 10:04 am
Thank you Orlando,
I got it but my team was fixed with SQl 2008.Thank you a lot for your help
Thanks,
Karthik
April 22, 2015 at 11:12 am
Just wanted to note that in SQL Server 2008 there's the DATE data type which won't include time. For hire dates it seems a better option.
Check the different Date and Time data types available and choose the correct one. https://msdn.microsoft.com/en-us/library/ms187752(v=sql.105).aspx
April 22, 2015 at 2:04 pm
ScottPletcher (4/22/2015)
Orlando Colamatteo (4/22/2015)
I'll second the point: definitely store your dates and times in a column of type DATETIME. If you make that a non-negotiable starting point the rest of this will be easy.Look into pushing formatting off to the application layer (if there is one) but if you have to provide formatted output direct from the database then FORMAT (added in SQL 2012) is a more intuitive way to output formatted dates in my opinion. One drawback of FORMAT is that it returns a VARCHAR(8000) by default so if you need type-safety you will need to cast the desired data-type:
DECLARE @dt DATETIME = '2015-04-22 13:00:00';
SELECT CAST(FORMAT(@dt, 'dd-MMM-yyyy h:mm:ss.ffftt') AS VARCHAR(50));
SELECT CONVERT(VARCHAR(50), @dt, 109);Again, in my opinion, the formatting mask shown in the first select is simpler in terms of interpreting the intent at first glance of the code than is style 109 in the second select.
The preferred format is: 'YYYYMMDD hh:mm:ss[.sss[ssss]]', because that is a universal format. Format yyyy-mm-dd will cause errors in regions where dates are normally yyyy-dd-mm.
Hey Scott, I am not following your comment. I was replicating the output offered by CONVERT with style 109 using FORMAT and a datestamp-mask. The OP wants military-style date/time for display.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 22, 2015 at 2:07 pm
Orlando Colamatteo (4/22/2015)
ScottPletcher (4/22/2015)
Orlando Colamatteo (4/22/2015)
I'll second the point: definitely store your dates and times in a column of type DATETIME. If you make that a non-negotiable starting point the rest of this will be easy.Look into pushing formatting off to the application layer (if there is one) but if you have to provide formatted output direct from the database then FORMAT (added in SQL 2012) is a more intuitive way to output formatted dates in my opinion. One drawback of FORMAT is that it returns a VARCHAR(8000) by default so if you need type-safety you will need to cast the desired data-type:
DECLARE @dt DATETIME = '2015-04-22 13:00:00';
SELECT CAST(FORMAT(@dt, 'dd-MMM-yyyy h:mm:ss.ffftt') AS VARCHAR(50));
SELECT CONVERT(VARCHAR(50), @dt, 109);Again, in my opinion, the formatting mask shown in the first select is simpler in terms of interpreting the intent at first glance of the code than is style 109 in the second select.
The preferred format is: 'YYYYMMDD hh:mm:ss[.sss[ssss]]', because that is a universal format. Format yyyy-mm-dd will cause errors in regions where dates are normally yyyy-dd-mm.
Hey Scott, I am not following your comment. I was replicating the output offered by CONVERT with style 109 using FORMAT and a datestamp-mask. The OP wants military-style date/time for display.
Right, good point, I understand that. But I was really talking about your local variable creation/assignment:
DECLARE @dt DATETIME = '2015-04-22 13:00:00'
Even there, you should use '20150422 13:00:00' instead, as it is 100% unambiguous to SQL Server no matter what date and/or language settings are in effect, where 2015-04-22 is not and can cause errors.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 22, 2015 at 9:17 pm
ScottPletcher (4/22/2015)
Orlando Colamatteo (4/22/2015)
ScottPletcher (4/22/2015)
Orlando Colamatteo (4/22/2015)
I'll second the point: definitely store your dates and times in a column of type DATETIME. If you make that a non-negotiable starting point the rest of this will be easy.Look into pushing formatting off to the application layer (if there is one) but if you have to provide formatted output direct from the database then FORMAT (added in SQL 2012) is a more intuitive way to output formatted dates in my opinion. One drawback of FORMAT is that it returns a VARCHAR(8000) by default so if you need type-safety you will need to cast the desired data-type:
DECLARE @dt DATETIME = '2015-04-22 13:00:00';
SELECT CAST(FORMAT(@dt, 'dd-MMM-yyyy h:mm:ss.ffftt') AS VARCHAR(50));
SELECT CONVERT(VARCHAR(50), @dt, 109);Again, in my opinion, the formatting mask shown in the first select is simpler in terms of interpreting the intent at first glance of the code than is style 109 in the second select.
The preferred format is: 'YYYYMMDD hh:mm:ss[.sss[ssss]]', because that is a universal format. Format yyyy-mm-dd will cause errors in regions where dates are normally yyyy-dd-mm.
Hey Scott, I am not following your comment. I was replicating the output offered by CONVERT with style 109 using FORMAT and a datestamp-mask. The OP wants military-style date/time for display.
Right, good point, I understand that. But I was really talking about your local variable creation/assignment:
DECLARE @dt DATETIME = '2015-04-22 13:00:00'
Even there, you should use '20150422 13:00:00' instead, as it is 100% unambiguous to SQL Server no matter what date and/or language settings are in effect, where 2015-04-22 is not and can cause errors.
That was an interesting thing to take away from my post, but OK, I'll play along. Here is a revised example using ISO 8601 date/time format that works regardless of the DATEFORMAT:
DECLARE @dt DATETIME = '2015-04-22T13:00:00';
SELECT CAST(FORMAT(@dt, 'dd-MMM-yyyy h:mm:ss.ffftt') AS VARCHAR(50));
SELECT CONVERT(VARCHAR(50), @dt, 109);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2015 at 8:04 am
Orlando Colamatteo (4/22/2015)
ScottPletcher (4/22/2015)
Orlando Colamatteo (4/22/2015)
ScottPletcher (4/22/2015)
Orlando Colamatteo (4/22/2015)
I'll second the point: definitely store your dates and times in a column of type DATETIME. If you make that a non-negotiable starting point the rest of this will be easy.Look into pushing formatting off to the application layer (if there is one) but if you have to provide formatted output direct from the database then FORMAT (added in SQL 2012) is a more intuitive way to output formatted dates in my opinion. One drawback of FORMAT is that it returns a VARCHAR(8000) by default so if you need type-safety you will need to cast the desired data-type:
DECLARE @dt DATETIME = '2015-04-22 13:00:00';
SELECT CAST(FORMAT(@dt, 'dd-MMM-yyyy h:mm:ss.ffftt') AS VARCHAR(50));
SELECT CONVERT(VARCHAR(50), @dt, 109);Again, in my opinion, the formatting mask shown in the first select is simpler in terms of interpreting the intent at first glance of the code than is style 109 in the second select.
The preferred format is: 'YYYYMMDD hh:mm:ss[.sss[ssss]]', because that is a universal format. Format yyyy-mm-dd will cause errors in regions where dates are normally yyyy-dd-mm.
Hey Scott, I am not following your comment. I was replicating the output offered by CONVERT with style 109 using FORMAT and a datestamp-mask. The OP wants military-style date/time for display.
Right, good point, I understand that. But I was really talking about your local variable creation/assignment:
DECLARE @dt DATETIME = '2015-04-22 13:00:00'
Even there, you should use '20150422 13:00:00' instead, as it is 100% unambiguous to SQL Server no matter what date and/or language settings are in effect, where 2015-04-22 is not and can cause errors.
That was an interesting thing to take away from my post, but OK, I'll play along. Here is a revised example using ISO 8601 date/time format that works regardless of the DATEFORMAT:
DECLARE @dt DATETIME = '2015-04-22T13:00:00';
SELECT CAST(FORMAT(@dt, 'dd-MMM-yyyy h:mm:ss.ffftt') AS VARCHAR(50));
SELECT CONVERT(VARCHAR(50), @dt, 109);
Sorry for focusing on that, but we have sites around the world and sometimes we need to exchange code. It prevents a lot of errors if the dates are acceptable regardless of local settings.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy