Conversion before save into database

This is how we want to convert datetime before we store it inside the database

DateTime.ParseExact("01/01/1900", "dd/MM/yyyy", new CultureInfo("ms-MY"));

SQL return list of month

Here is another query which i have modified from my previous post.

This query will list out list of months

SELECT Month(GETDATE()) as MonthNum
UNION
SELECT Month(GETDATE()) - 1 as MonthNum
UNION
SELECT Month(GETDATE()) - 2 as MonthNum
UNION
SELECT Month(GETDATE()) - 3 as MonthNum
UNION
SELECT Month(GETDATE()) - 4 as MonthNum
UNION
SELECT Month(GETDATE()) - 5 as MonthNum
UNION
SELECT Month(GETDATE()) - 6 as MonthNum
UNION
SELECT Month(GETDATE()) - 7 as MonthNum
UNION
SELECT Month(GETDATE()) - 8 as MonthNum
UNION
SELECT Month(GETDATE()) - 9 as MonthNum
UNION
SELECT Month(GETDATE()) - 10 as MonthNum
UNION
SELECT Month(GETDATE()) - 11 as MonthNum
ORDER BY MonthNum DESC


and here is some query to convert month number to month name

Convert Month Number to name SQLServerCurry


And here is a full query:

SELECT Month(GETDATE()) as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE()),-1)) as MonthName
UNION
SELECT Month(GETDATE())-1 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-1,-1)) as MonthName
UNION
SELECT Month(GETDATE()) - 2 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-2,-2)) as MonthName
UNION
SELECT Month(GETDATE()) - 3 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-3,-3)) as MonthName
UNION
SELECT Month(GETDATE()) - 4 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-4,-4)) as MonthName
UNION
SELECT Month(GETDATE()) - 5 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-5,-5)) as MonthName
UNION
SELECT Month(GETDATE()) - 6 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-6,-6)) as MonthName
UNION
SELECT Month(GETDATE()) - 7 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-7,-7)) as MonthName
UNION
SELECT Month(GETDATE()) - 8 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-8,-8)) as MonthName
UNION
SELECT Month(GETDATE()) - 9 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-9,-9)) as MonthName
UNION
SELECT Month(GETDATE()) - 10 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-10,-10)) as MonthName
UNION
SELECT Month(GETDATE()) - 11 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-11,-11)) as MonthName
ORDER BY MonthNum DESC

List of Year for MSSQL

Hi, i am current doing some project and i need to get list of year dynamically.

Meaning, i don't want to recode or update list of year in a drop down list

So, i google and found this article

SQL return list of year - StackOverFlow.com

and i used in store procedure and call it within sqldatasource ;)


create a new view and paste this code

SELECT YEAR(GETDATE()) as YearNum
UNION
SELECT YEAR(GETDATE()) - 1 as YearNum
UNION
SELECT YEAR(GETDATE()) - 2 as YearNum
UNION
SELECT YEAR(GETDATE()) - 3 as YearNum
UNION
SELECT YEAR(GETDATE()) - 4 as YearNum
UNION
SELECT YEAR(GETDATE()) - 5 as YearNum
UNION
SELECT YEAR(GETDATE()) - 6 as YearNum
UNION
SELECT YEAR(GETDATE()) - 7 as YearNum
UNION
SELECT YEAR(GETDATE()) - 8 as YearNum
UNION
SELECT YEAR(GETDATE()) - 9 as YearNum
UNION
SELECT YEAR(GETDATE()) - 10 as YearNum
ORDER BY YearNum DESC

Get Date and Time separately from SQL

Situation: You have a column in your table type DateTime. But you just want to view the date or time only. This is how it can be done.

SQL query

Select convert(varchar, [your column date], 3)
from [your table name]

will return result in format dd/mm/yy

Select select convert(varchar, [your column date], 8 )
from [your table name]

will return result in format hh:mm:ss

Here are list of format which you can refer:

Date Formats
Format No.	SQL Query	Output
1	select convert(varchar, [your column date], 1) from [your table name]	12/30/08
2	select convert(varchar, [your column date], 2) from [your table name]	08.12.30
3	select convert(varchar, [your column date], 3) from [your table name]	30/12/08
4	select convert(varchar, [your column date], 4) from [your table name]	30.12.08
5	select convert(varchar, [your column date], 5) from [your table name]	30-12-08
6	select convert(varchar, [your column date], 6) from [your table name]	30 Dec 08
7	select convert(varchar, [your column date], 7) from [your table name]	Dec 30, 08
10	select convert(varchar, [your column date], 10) from [your table name]	12-30-08
11	select convert(varchar, [your column date], 11) from [your table name]	08/12/30
101	select convert(varchar, [your column date], 101) from [your table name]	12/30/2008
102	select convert(varchar, [your column date], 102) from [your table name]	2008.12.30
103	select convert(varchar, [your column date], 103) from [your table name]	30/12/2008
104	select convert(varchar, [your column date], 104) from [your table name]	30.12.2008
105	select convert(varchar, [your column date], 105) from [your table name]	30-12-2008
106	select convert(varchar, [your column date], 106) from [your table name]	30 Dec 2008
107	select convert(varchar, [your column date], 107) from [your table name]	Dec 30, 2008
110	select convert(varchar, [your column date], 110) from [your table name]	12-30-2008
111	select convert(varchar, [your column date], 111) from [your table name]	2008/12/30

Time Formats
Format No.	SQL Query	Output
8 or 108	select convert(varchar, [your column date], 8 ) from [your table name]	00:40:50
9 or 109	select convert(varchar, [your column date], 9) from [your table name]	Dec 30 2006 12:40:50:840AM
14 or 114	select convert(varchar, [your column date], 14) from [your table name]	00:40:50:840